mysql - 如何将两个查询与 case 语句结合起来

Question

如何用case语句组合两个查询

查询1

SELECT   DATE(created_at),COUNT(DISTINCT user_id) AS count1 
FROM     user
WHERE    by_user = '3'  
AND      DATE(created_at) BETWEEN CURRENT_DATE-2 AND CURRENT_DATE
GROUP BY DATE(created_at) 

查询2

SELECT   DATE(created_at),COUNT(DISTINCT user_id) AS count2 
FROM     USER  
WHERE    by_user <> '3'  
AND      DATE(created_at) BETWEEN CURRENT_DATE-2 AND CURRENT_DATE
GROUP BY DATE(created_at) 

预期输出:

+---------------------+--------+--------+
| date(created_at)    | count1 | count2 |
+---------------------+--------+--------+
| 30/12/2019,12:00 AM | 4000   | 5000   |
| 29/12/2019,12:00 AM | 2434   | 5432   |
+---------------------+--------+--------+

Answer 1

使用条件聚合:

select date(created_at), 
  count(distinct case when by_user = '3' then user_id end) as count1, 
  count(distinct case when by_user <> '3' then user_id end) as count2 
from user  
where date(created_at) between current_date-2 and current_date
group by date(created_at) 

条件where by_user = '3'或 where by_user <> '3'使用 CASE 检查表达式，因此它们从 WHERE 中删除条款。