请教这两个sql语句查询出来的结果为什么不一样

[土豆王]

SELECT COUNT(a.uid) AS last_1_2_count FROM
(
    SELECT DISTINCT uid,MAX(hfdate) AS max_hfdate,POSITION,checked FROM hrdk.dk_hf GROUP BY uid
) AS a
WHERE (a.max_hfdate BETWEEN UNIX_TIMESTAMP(DATE_SUB(CURDATE(),INTERVAL 2 DAY)) AND UNIX_TIMESTAMP(DATE_SUB(CURDATE(),INTERVAL 1 DAY)))
AND a.position>0 AND a.checked=2

这个结果是：36745
*****************************************************************************

SELECT COUNT(a.uid) AS last_1_2_count FROM
(
    SELECT DISTINCT uid,MAX(hfdate) AS max_hfdate,POSITION,checked FROM hrdk.dk_hf WHERE POSITION>0 AND checked=2 GROUP BY uid 
    HAVING max_hfdate BETWEEN UNIX_TIMESTAMP(DATE_SUB(CURDATE(),INTERVAL 2 DAY)) AND UNIX_TIMESTAMP(DATE_SUB(CURDATE(),INTERVAL 1 DAY))
)
AS a 

这个结果是：37067

求大神指导，问题是出在having上吗？

[ACMAIN_CHM]

与HAVING无关，注意你的 AND a.position>0 AND a.checked=2 所在的位置不同。 第一个语句中，如果hfdate最大的那条记录不符合 a.position>0 AND a.checked=2 则MAX最大值会与第二条不同。这时第一条取到的max(hfdate)则是 符合 a.position>0 AND a.checked=2 条件的最大值。而这个值不符合max_hfdate BETWEEN UNIX_TIMESTAMP(DATE_SUB(CURDATE(),INTERVAL 2 DAY)) AND UNIX_TIMESTAMP(DATE_SUB(CURDATE(),INTERVAL 1 DAY))条件。

[rucypli]

一个先group by 一个后group by 而where过滤条件不是group by的那个字段

[WWWWA]

SELECT DISTINCT uid,MAX(hfdate) AS max_hfdate,POSITION,checked FROM hrdk.dk_hf GROUP BY uid SELECT DISTINCT uid,MAX(hfdate) AS max_hfdate,POSITION,checked FROM hrdk.dk_hf WHERE POSITION>0 AND checked=2 GROUP BY uid HAVING max_hfdate BETWEEN UNIX_TIMESTAMP(DATE_SUB(CURDATE(),INTERVAL 2 DAY)) AND UNIX_TIMESTAMP(DATE_SUB(CURDATE(),INTERVAL 1 DAY)) 这2个SQL语句条件都　不同，结果肯定有差异

[土豆王]

引用 1 楼 ACMAIN_CHM 的回复:

与HAVING无关，注意你的 AND a.position>0 AND a.checked=2 所在的位置不同。 第一个语句中，如果hfdate最大的那条记录不符合 a.position>0 AND a.checked=2 则MAX最大值会与第二条不同。这时第一条取到的max(hfdate)则是 符合 a.position>0 AND a.checked=2 条件的最大值。而这个值不符合max_hfdate BETWEEN UNIX_TIMESTAMP(DATE_SUB(CURDATE(),INTERVAL 2 DAY)) AND UNIX_TIMESTAMP(DATE_SUB(CURDATE(),INTERVAL 1 DAY))条件。

谢谢您的解答，80分就给你了吧，哈哈