如何对字符串中的ID进行查询? [英] How can I run a query on IDs in a string?

问题描述

我有一个带有此列的表A:

I have a table A with this column:

  IDS(VARCHAR)
  1|56|23

我需要运行以下查询:

  select TEST from TEXTS where ID in ( select IDS from A where A.ID = xxx )

TEXTS.ID是INTEGER.如何将字符串A.IDS拆分为多个整数以进行联接?

TEXTS.ID is an INTEGER. How can I split the string A.IDS into several ints for the join?

必须在MySQL和Oracle上工作.首选SQL99.

Must work on MySQL and Oracle. SQL99 preferred.

推荐答案

首先，您不应该在列中存储这样的数据.您应该将其拆分到一个单独的表中，然后您将具有正常的联接，而不是这个问题.

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:

1. 将数字转换为字符串
2. 在它的前面和后面用|(您的分隔符)字符将其填充(在下面我会告诉您原因)
3. 前后使用相同的分隔符粘贴您要查找的文本
4. 在其上执行LIKE

1. Convert the number to a string
2. Pad it with the | (your separator) character, before it, and after it (I'll tell you why below)
3. Pad the text you're looking in with the same separator, before and after
4. Do a 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.

这篇关于如何对字符串中的ID进行查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！