Mysql在单个查询中计数两行

Question

是否可以在单个查询中计数两行?现在我正在使用两个查询:

SELECT count(draft) AS draftCount FROM projects WHERE draft = 1 AND user_id = ?

和另一个:

SELECT count(published) AS publishedCount FROM projects WHERE published = 1 AND user_id = ?

我想把这两个查询合并成一个，是可能的吗?

Answer 1

SELECT SUM(published) AS publishedCount,
       SUM(draft) AS draftCount
FROM projects
WHERE user_id = ?

据我了解，您的查询中published和draft的值为true, 1为true, 0为false。所以你可以只SUM他们得到相同的结果。

Answer 2

试试这个

SELECT count(case when draft = 1 then draft end) AS draftCount,
count(case when published = 1 then published end) AS publishedCount 
FROM projects 
WHERE user_id = ?

Answer 3

Try UNION:

(SELECT count(draft) FROM projects WHERE draft = 1 AND user_id = 4)
UNION
(SELECT count(published) FROM projects WHERE published = 1 AND user_id = 4);