具有行存在

Question

以下数据库样本

id | date | customer | ... 
----------------------------------------- 
1  2016-07-05  1 
2  2016-07-05  2 
3  2016-07-06  1 
4  2016-07-07  1 
5  2016-07-07  2 

我想在2016-07-07选择其中有一个条目中的所有客户，但不在2016-07-06。

起初，我以为我会做到这一点使用WHERE：

SELECT * FROM table 
    WHERE EXISTS (SELECT * FROM table WHERE date = '2016-07-07') 
    AND NOT EXISTS (SELECT * FROM table WHERE date = '2016-07-06') 
    GROUP BY customer 

但由于WHERE之前GROUP BY执行，结果只能由空 - 那里是2016-07-06在table的记录。

所以，使用HAVING，我该怎么做？我在HAVING条款中检查行存在时遇到困难。喜欢的东西：

SELECT * FROM table 
    GROUP BY customer 
    HAVING exists date '2016-07-07' 
    AND not exists date '2016-07-06' 

Answer 1

集团由customer，并采取只有那些群体具有至少一个date条目2016-07-07并没有为2016-07-06

SELECT customer 
FROM your_table 
GROUP BY customer 
HAVING sum(date = '2016-07-07') > 0 
    AND sum(date = '2016-07-06') = 0 

Answer 2

SELECT customer FROM table t 
    WHERE EXISTS (SELECT * FROM table t1 
       WHERE date = '2016-07-07' AND t.customer = t1.customer) 
    AND NOT EXISTS (SELECT * FROM table t2 
        WHERE date = '2016-07-06' AND t.customer = t2.customer) 
    GROUP BY customer