如何优化筛选双日期有订单且无中间订单的SQL查询？

先回顾下你的场景：
我们有一个orders表，结构和测试数据如下：

CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT, created_at DATE) ;
INSERT INTO orders (customer_id, created_at) VALUES 
(1, '2019-10-09'), (1, '2019-10-01'), (1, '2019-08-09'), 
(2, '2019-10-09'), (2, '2019-10-09'), 
(3, '2019-09-09'), (3, '2019-08-09'), 
(4, '2019-08-09'), (4, '2019-08-09'), 
(5, '2019-10-09'), (5, '2019-10-09'), (5, '2019-08-09') ;

需求是：筛选出在2019-08-09和2019-10-09各至少有1笔订单，且这两个日期之间（2019-08-10到2019-10-08）没有任何订单的客户，示例中只有customer_id=5符合条件。

你已经用多个EXISTS子句实现了需求，写法如下：

SELECT DISTINCT(customer_id) 
FROM orders o1 
WHERE EXISTS (SELECT 1 FROM orders o2 WHERE o1.customer_id = o2.customer_id AND o2.created_at = '2019-10-09') 
  AND EXISTS (SELECT 1 FROM orders o2 WHERE o1.customer_id = o2.customer_id AND o2.created_at = '2019-08-09') 
  AND NOT EXISTS (SELECT 1 FROM orders o2 WHERE o1.customer_id = o2.customer_id AND o2.created_at BETWEEN '2019-08-10' AND '2019-10-08')

这个写法逻辑清晰，容易理解，但确实可以优化得更简洁高效——推荐用分组聚合+条件判断的方式，只需要扫描一次表就能完成筛选，性能更优：

方案一：使用BOOL_OR聚合函数（PostgreSQL专属） #

PostgreSQL提供了BOOL_OR函数，只要分组内有任意一行满足条件就返回true，写法非常简洁：

SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING BOOL_OR(created_at = '2019-08-09')
   AND BOOL_OR(created_at = '2019-10-09')
   AND NOT BOOL_OR(created_at BETWEEN '2019-08-10' AND '2019-10-08');

方案二：通用SQL写法（适配多数数据库） #

如果需要兼容其他数据库，可以用COUNT条件聚合来实现：

SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(CASE WHEN created_at = '2019-08-09' THEN 1 END) >= 1
   AND COUNT(CASE WHEN created_at = '2019-10-09' THEN 1 END) >= 1
   AND COUNT(CASE WHEN created_at BETWEEN '2019-08-10' AND '2019-10-08' THEN 1 END) = 0;

为什么这两个方案更高效？ #

你的原写法用了三次EXISTS子查询，虽然PostgreSQL的查询优化器可能会做一些优化，但本质上是多次关联扫描表；而分组聚合的方式只需要对orders表进行一次全表扫描（如果有合适的索引，比如(customer_id, created_at)，还能进一步提速），逻辑更紧凑，性能表现更稳定，尤其在数据量大的时候优势更明显。

内容的提问来源于stack exchange，提问作者ckg61386