mysql - SQL : 2 distinct counts with different where clauses on same field

Question

如何将下面的 2 个查询合并为一个查询？

select count(distinct user_id) as action_1_count, date  
from table    
where action = "action_1"  
group by date;

select count(distinct user_id) as action_2_count, date
from table  
where action = "action_2"  
group by date;

Answer 1

像这样合并上面的查询:

SELECT date,
         CASE WHEN action  = "action_1" THEN COUNT(user_id) END AS Action1Count,
         CASE WHEN action  = "action_2" THEN COUNT(user_id) END AS Action2Count
  FROM table    
  WHERE action IN ("action_1", "action_2")
  GROUP BY date