我正在寻找一种更有效的方法来构建此查询(双子查询让我感到畏缩):

SELECT contact_id FROM (
  SELECT * FROM (
    SELECT mr.contact_id, di.district
    FROM recipients mr
    JOIN address a ON mr.contact_id = a.contact_id
    JOIN district_values di ON a.id = di.entity_id
    WHERE mr.mid = 29
    ORDER BY di.district DESC ) addrSingle
  GROUP BY mr.contact_id ) addrNull
WHERE di.district IS NULL

让我解释一下这里发生了什么。

收件人持有联系人列表。每个联系人可能有多个地址。每个地址都有一个相关的 District_values 表。我需要检索所有地址的 District_values.district 列为空的联系人。

例如:
Contact A
Address 1.district = 4
Address 2.district = null
= don't include

Contact B
Address 1.district = null
= include

Contact C
Address 1.district = null
Address 2.district = 3
= don't include

我现有查询的逻辑如下:
  • 检索具有相关地址和地区的联系人,排序以便任何具有非空值的地址首先排序
  • 应用 group by 所以我减少到单个联系人记录,如果保留了一个地区的地址
  • 应用 where 子句删除至少具有一个区值的地址

  • 它有效——只是有点难看。

    最佳答案

    你可以试试这个,使用 LEFT JOIN 并计算为零的相关记录。

    SELECT mr.contact_id
        FROM recipients mr
    LEFT JOIN address a ON mr.contact_id = a.contact_id
    LEFT JOIN district_values di ON a.id = di.entity_id
        WHERE mr.mid = 29
    GROUP BY mr.contact_id
        HAVING COUNT(a.*) = 0 AND COUNT(di.*) = 0
    

    关于mysql - 检索相关表中所有值为空的记录,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/9047056/

    10-15 02:05