如何对字符串中的 ID 运行查询？

Question

我有一个包含此列的表 A：

  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 拆分为多个整数以进行连接？

必须在 MySQL 和 Oracle 上运行。 SQL99首选。

Answer 1

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

话虽如此，您需要做的是以下操作：

1. 将数字转换为字符串
2. 用 | （您的分隔符）字符填充它，在它之前和之后（我会告诉你为什么下面）
3. 用相同的分隔符填充您要查找的文本，前后
4. 执行 LIKE

这会运行缓慢！

这是执行您想要的 SQL （假设所有运算符和函数都在您的 SQL 方言中工作，您不会说这是什么类型的数据库引擎）：

SELECT
    TEXT   -- assuming this was misspelt?
FROM
    TEXTS  -- and this as well?
    JOIN A ON
        '|' + A.IDS + '|' LIKE '%|' + CONVERT(TEXTS.ID) + '|%'

您需要在前后用分隔符填充两者的原因是：如果您是寻找数字 5？您需要确保它不会仅仅因为它包含数字而意外地适合 56 数字。

基本上，我们会这样做：

... '|1|56|23|' LIKE '%|56|%'

如果 A 中只有 1 行，如果你这样做，它可能会运行得更快（但我不确定，你需要测量它）：

SELECT
    TEXT   -- assuming this was misspelt?
FROM
    TEXTS  -- and this as well?
WHERE
    (SELECT '|' + IDS + '|' FROM A) LIKE '%|' + CONVERT(TEXTS.ID) + '|%'

如果你的 A 中有很多行TEXTS 表中，值得努力添加代码来生成适当的 SQL，方法是首先从 A 表中检索值，使用 IN 构造适当的 SQL 并使用它：

SELECT
    TEXT   -- assuming this was misspelt?
FROM
    TEXTS  -- and this as well?
WHERE
    ID IN (1, 56, 23)

这将运行得更快，因为现在它可以在此查询上使用索引。

如果您将 A.ID 作为一列，并将值作为单独的行，则执行查询的方式如下：

SELECT
    TEXT   -- assuming this was misspelt?
FROM
    TEXTS  -- and this as well?
    INNER JOIN A ON TEXTS.ID = A.ID

这将比前一个查询运行得稍慢，但在前一个查询中，您必须首先检索 A.IDS，构建查询，并冒着生成必须编译的新执行计划的风险。