SQL语句查询：前7天下单【1，4】次的用户并在近7天下单0次。（left join on）

 

解题思路：

利用左连接查询

1、把13-19号下单数量1-4笔订单的用户pid查询出来，当做表1: bb

 SELECT * FROM ( SELECT pid, orders FROM ( SELECT * FROM ( SELECT pid, COUNT(1) AS orders FROM 订单表 WHERE (pay_day >= '2020-04-13' AND pay_day <= '2020-04-19' AND status = '2') GROUP BY pid ) b WHERE orders >= 1 AND orders <= 4 ) bb 

2、把20-26号所有下单的用户pid查询出来，当做表2:a

 SELECT pid, orders FROM ( SELECT pid, COUNT(1) AS orders FROM 订单表 WHERE (pay_day >= '2020-04-20' AND pay_day <= '2020-04-26' AND status = '2') GROUP BY pid ) a

3、利用左连接查询，将左边有的数据，而右边是没有的，table2.pid =空 ,查询出来

SELECT COUNT(*) FROM ( SELECT * FROM ( SELECT pid, orders FROM ( SELECT * FROM ( SELECT pid, COUNT(1) AS orders FROM 订单表 WHERE (pay_day >= '2020-04-13' AND pay_day <= '2020-04-19' AND status = '2') GROUP BY pid ) b WHERE orders >= 1 AND orders <= 4 ) bb ) table1 LEFT JOIN ( SELECT pid, orders FROM ( SELECT pid, COUNT(1) AS orders FROM 订单表 WHERE (pay_day >= '2020-04-20' AND pay_day <= '2020-04-26' AND status = '2') GROUP BY pid ) a ) table2 ON table1.pid = table2.pid ) WHERE table2.pid = ' '