MySQL select count count different data from same tables 最佳方法

Question

我正在尝试获取同一个表中不同 where 子句中的值的计数。哪个是最好的方法

SELECT * FROM 
  (SELECT COUNT(id) FROM table WHERE post_id = 123 AND action_type IN(1,3,7,9,10)) as 'a',
  (SELECT COUNT(id) FROM table WHERE post_id = 123 AND action_type IN(2,7,8,18)) as 'b'

或

SELECT COUNT(id) FROM table WHERE post_id = 123 AND action_type IN(1,3,7,9,10)
SELECT COUNT(id) FROM table WHERE post_id = 123 AND action_type IN(2,7,8,18)

注意:表格包含数百万个值

Answer 1

您可以使用条件聚合，以便在没有子查询的情况下在一个查询中获取数据:

SELECT COUNT(CASE WHEN action_type IN(1,3,7,9,10) THEN id END) AS count_1,
       COUNT(CASE WHEN action_type IN(2,7,8,18) THEN id END) AS count_2
FROM table 
WHERE id = 123