更新时间:2022-05-28 00:39:46
首先,您不应该在列中存储这样的数据.您应该将其拆分到一个单独的表中,然后您将具有正常的联接,而不是这个问题.
First of all, you should not store data like this in a column. You should split that out into a separate table, then you would have a normal join, and not this problem.
话虽如此,您要做的是以下事情:
Having said that, what you have to do is the following:
|
(您的分隔符)字符将其填充(在下面我会告诉您原因)LIKE
|
(your separator) character, before it, and after it (I'll tell you why below)LIKE
on it运行速度会很慢!
这是满足您需要的SQL(假设所有运算符和函数都在您的SQL方言中工作,而您没有说这是哪种数据库引擎):
Here's the SQL that does what you want (assuming all the operators and functions work in your SQL dialect, you don't say what kind of database engine this is):
SELECT
TEXT -- assuming this was misspelt?
FROM
TEXTS -- and this as well?
JOIN A ON
'|' + A.IDS + '|' LIKE '%|' + CONVERT(TEXTS.ID) + '|%'
您需要在分隔符前后插入两个分隔符的原因是:如果您要查找数字5,该怎么办?您需要确保它不会偶然地适合56
数字,只是因为它包含数字.
The reason why you need to pad the two with the separator before and after is this: what if you're looking for the number 5? You need to ensure it wouldn't accidentally fit the 56
number, just because it contained the digit.
基本上,我们会这样做:
Basically, we will do this:
... '|1|56|23|' LIKE '%|56|%'
如果A中只有1行,那么这样做可能会运行得更快(但我不确定,您需要对其进行测量):
If there is ever only going to be 1 row in A, it might run faster if you do this (but I am not sure, you would need to measure it):
SELECT
TEXT -- assuming this was misspelt?
FROM
TEXTS -- and this as well?
WHERE
(SELECT '|' + IDS + '|' FROM A) LIKE '%|' + CONVERT(TEXTS.ID) + '|%'
如果TEXTS
表中有很多行,则值得花费精力来添加代码以生成适当的SQL,方法是先从A
表中检索值,然后用IN
构造适当的SQL,然后改用它:
If there are many rows in your TEXTS
table, it will be worth the effort to add code to generate the appropriate SQL by first retrieving the values from the A
table, construct an appropriate SQL with IN
and use that instead:
SELECT
TEXT -- assuming this was misspelt?
FROM
TEXTS -- and this as well?
WHERE
ID IN (1, 56, 23)
这将运行得更快,因为现在它可以在该查询上使用索引.
This will run much faster since now it can use an index on this query.
如果您将A.ID作为一列,并将值作为单独的行,则查询方法如下:
If you had A.ID as a column, and the values as separate rows, here's how you would do the query:
SELECT
TEXT -- assuming this was misspelt?
FROM
TEXTS -- and this as well?
INNER JOIN A ON TEXTS.ID = A.ID
这将比上一个慢一些,但是在上一个中,您必须先检索A.IDS
,构建查询并冒产生必须编译的新执行计划的开销.
This will run slightly slower than the previous one, but in the previous one you have overhead in having to first retrieve A.IDS
, build the query, and risk producing a new execution plan that has to be compiled.