sql - PostgreSQL - 避免重复子查询来进行空检查

Question

我正在尝试识别一个表中的某些项目，这些项目在手动输入的文本中引用了另一个表中项目的标识符。数据结构设计不佳，无法用于表之间的简单连接；它们不同步。

我有这个并且它有效，只是不是很愉快:

SELECT i.id, (
     SELECT c.text
     FROM charges c
     WHERE c.text LIKE CONCAT('%', i.order, '%')
     AND c.state = 'paid'
) AS "charge text"
FROM items i
WHERE i.state = 'not paid'
AND (
     SELECT c.text
     FROM charges c
     WHERE c.text LIKE CONCAT('%', i.order, '%')
     AND c.state = 'paid'
) IS NOT NULL
ORDER BY id

charges 中的示例数据:

id    state    text
------------------------------------------
1     paid     Lorem ipsum ABC123 sit amet
2     not paid DEF456 lorem ipsum sit amet

items 中的示例数据:

id    state    order
---------------------
1     not paid ABC123
2     not paid DEF456

目前，我需要子查询的第二个实例来删除由于检查每个 i.number 而选择 NULL 原因的实例。我想避免子查询出现两次，但似乎我不能使用 common table expression(WITH，如 this answer 中所示)，因为它会在开始时定义，所以我不能'将 i.number 传递给它。我可能错了，因为我以前没有使用过 CTE。

所以我想我有两个问题 - 首先，我可以定义一次子查询，然后只检查结果是否为 NOT NULL；其次，我是不是做错了，还有更好的查询方法可以避免进行该检查吗？

Answer 1

将其正确地写为连接。当您的结果集中不需要子查询的结果时，最好使用相关子查询。在这种情况下，您需要，所以只需加入:

SELECT
    i.id,
    c.txt
FROM items i
    INNER JOIN charges c ON
        c.text like CONCAT('%', i.order , '%')
WHERE
    c.state = 'paid'
    AND i.state = 'unpaid'

您可以添加 AND c.txt IS NOT NULL到那个 WHERE 子句，但是使用 INNER JOIN 它可能不太可能捕捉到任何尚未被连接本身抑制的东西。

此外，我取消了子查询，因为您没有聚合或执行主查询本身无法完成的任何其他逻辑。