MySQL - WHERE和GROUP BY语句冲突

Question

我有一个返回某些数据计数的查询，但我不想要其中包含空值的数据……

例如，代码将点击系统中的统计数据滚动到一个表中。

SELECT sh.dropid,
COUNT(DISTINCT IF(sh2.`SentDate` IS NOT NULL, sh2.`subid`, NULL)) AS sentCount,
COUNT(DISTINCT IF(sh2.`Open` = 1, sh2.`subid`, NULL)) AS openCount,
COUNT(DISTINCT IF(sh2.`Click` = 1, sh2.`subhistid`, NULL)) AS clickCount,
COUNT(DISTINCT IF(sh2.`Bounced` = 1, sh2.`subid`, NULL)) AS bounceCount,
COUNT(DISTINCT IF(sh2.`Unsubscribed` = 1, sh2.`subid`, NULL)) AS unsubCount,
COUNT(DISTINCT IF(sh2.`Abuse` = 1, sh2.`subid`, NULL)) AS abuseCount
FROM subscriberhistory sh
INNER JOIN subscriberhistory sh2 ON sh.subid = sh2.subid
WHERE sh.SentDate >= '#runDate# #lastRunTime#'
AND sh.dropid IS NOT NULL
AND sh.dropid != ""
OR (sh.SentDate IS NULL AND sh.OpenDate >= '#runDate# #lastRunTime#')
OR (sh.SentDate IS NULL AND sh.ClickDate >= '#runDate# #lastRunTime#')
OR (sh.SentDate IS NULL AND sh.UnsubscribeDate >= '#runDate# #lastRunTime#')
OR (sh.SentDate IS NULL AND sh.BouncedDate >= '#runDate# #lastRunTime#')
OR (sh.SentDate IS NULL AND sh.AbuseDate >= '#runDate# #lastRunTime#')
GROUP BY dropid
ORDER BY sentCount DESC

编辑:已更正的查询

SELECT sh.dropid,
COUNT(DISTINCT IF(sh2.`SentDate` IS NOT NULL, sh2.`subid`, NULL)) AS sentCount,
COUNT(DISTINCT IF(sh2.`Open` = 1, sh2.`subid`, NULL)) AS openCount,
COUNT(DISTINCT IF(sh2.`Click` = 1, sh2.`subid`, NULL)) AS clickCount,
COUNT(DISTINCT IF(sh2.`Bounced` = 1, sh2.`subid`, NULL)) AS bounceCount,  
COUNT(DISTINCT IF(sh2.`Unsubscribed` = 1, sh2.`subid`, NULL)) AS unsubCount,
COUNT(DISTINCT IF(sh2.`Abuse` = 1, sh2.`subid`, NULL)) AS abuseCount
FROM subscriberhistory sh
INNER JOIN subscriberhistory sh2 ON sh.subid = sh2.subid
WHERE sh.dropid IS NOT NULL AND sh.dropid != ""
AND ((sh.SentDate >= '2012-04-10 14:15') 
OR (sh.SentDate IS NULL AND sh.OpenDate >= '2012-04-10 14:15')
OR (sh.SentDate IS NULL AND sh.ClickDate >= '2012-04-10 14:15')
OR (sh.SentDate IS NULL AND sh.UnsubscribeDate >= '2012-04-10 14:15')
OR (sh.SentDate IS NULL AND sh.BouncedDate >= '2012-04-10 14:15')
OR (sh.SentDate IS NULL AND sh.AbuseDate >= '2012-04-10 14:15'))
GROUP BY sh.dropid
ORDER BY sentCount DESC

返回的记录集的示例如下所示...

(dropid) sent opens  clicks
400      2    3      4
401      2    3      6
NULL     2    3      4

同样，目标是将空数据排除在前面的记录集中。谁能给我解释一下为什么会发生这种行为，以及如何解决它。

Answer 1

正如我在注释中提到的- NULL行通过WHERE语句中的OR条件悄悄进入。

考虑一下：

WHERE sh.dropid IS NOT NULL
AND   sh.dropid != ""
OR    ( some.other.condition )

假设您有一行sh.dropid为NULL，但仍然满足some.other.condition -那么由于OR，整个WHERE子句的计算结果将为TRUE。

解决方案-添加括号。例如(取决于你想要的逻辑)：

WHERE sh.dropid IS NOT NULL
  AND sh.dropid != ""
  AND (   one.condition
       OR second.condition
       OR third.condition
       )