SQL:基于聚合函数过滤组

Question

使用MySQL

所以我正在尝试改进我编写的查询。我当前的查询有效，但我觉得我可以更有效率

本质上，我有一个表格，列出了“谁与谁交谈以及交谈量”。

记录如下所示:

电子邮件名称状态计数 prod_ref(我将在帖子末尾发布一个包含示例输出的示例集)PK = 电子邮件和姓名name 是特定于运行该程序的组织的内部电子邮件地址。 (即 sue@innatech 变为 sue)

我最初想要的是一个查询，该查询将返回每个电子邮件地址计数最高的记录。

我能想到的最好的办法是:

SELECT email, name
FROM email_tbl AS a INNER JOIN
     (SELECT email, MAX(count) cnt
      FROM email_tbl
      GROUP BY email) AS b ON a.email = b.email AND a.count = r.cnt

这看起来相当优化。然后，我可以通过向子查询添加 HAVING 子句来指定 SUM(count) > 20 等内容来进一步过滤结果。

令人讨厌的部分是状态字段。 (我知道，存在一些数据重复问题，这些问题会破坏正确的 ER 样式，但由于某些原因，我只允许使用一张表)。

状态字段描述是否应忽略特定电子邮件。理论上，电子邮件的所有记录都应该具有相同的字段，但我想考虑这种情况不正确的情况。基本上，如果存在状态为“忽略”的记录，我想丢弃该记录上的电子邮件。

我能想到的唯一方法是另一个子查询，通过添加到末尾

WHERE a.email NOT IN 
   (SELECT DISTINCT email
    FROM email_tbl
    WHERE status = 'ignore')

它有效......但我总是在市场上改进我的工具，所以我想知道是否有更好的方法来实现这一点。

附录A示例集

email_tbl

bob@bob.com;phil;good;12
bob@bob.com;sue;good;5
-----------------------
rob@bob.com;phil;good;2
rob@bob.com;sue;good;2
rob@bob.com;fred;good;8
-----------------------
dan@bob.com;phil;good;5
dan@bob.com;sue;ignore;4

会返回

bob@bob.com;phil
rob@bob.com;fred

Answer 1

只有几个即兴查询，因此您必须测试性能:

使用 NOT EXISTS 而不是 IN 至少应该比您所拥有的更快，因为它可以在找到匹配项后立即停止处理子查询。但它是相关的，所以你必须进行测试。

SELECT
    email,
    name
FROM
    Email_Tbl AS T1
INNER JOIN
(
    SELECT email, MAX(count) cnt
    FROM email_tbl
    GROUP BY email
) AS SQ ON
    SQ.email = T1.email AND
    SQ.cnt = T1.count
WHERE
    NOT EXISTS
    (
        SELECT *
        FROM Email_Tbl T2
        WHERE T2.email = T1.email
          AND T2.status = 'Ignore'
    )

<小时/>

SELECT
    email,
    name
FROM
    Email_Tbl T1
LEFT OUTER JOIN Email_Tbl T2 ON
    T2.email = T1.email AND
    (
        T2.count > T1.count OR
        T2.status = 'Ignore'
    )
WHERE
    T2.email IS NULL

<小时/>

SELECT
    email,
    name
FROM
    Email_Tbl T1
LEFT OUTER JOIN Email_Tbl T2 ON
    T2.email = T1.email AND
    T2.count > T1.count
LEFT OUTER JOIN Email_Tbl T3 ON
    T3.email = T1.email AND
    T3.status = 'Ignore'
WHERE
    T2.email IS NULL OR
    T3.email IS NULL