mysql - 选择从 1 列计数到 2 列

Question

我有一个表，其中有一个名为 type 的列，它包含两个单词之一 upvote 或 downvote 我想按 a 分组。 question_id 然后得到 2 列，每列都有一个计数。

select a.*, u.* from answers a
left join users u using(user_id)
left join vote_history v using(answer_id)
where a.question_id = 4 and deleted < 4
group by v.question_id
order by votes desc

示例选择

ColumnA  | ColumnB  | upvotes | downvotes
---------+----------+---------+----------
record1A | record2B | 3       | 2
record2A | record2B | 2       | 5

我是否可以在不执行多个查询且不执行子查询的情况下执行此操作？

Answer 1

这称为枢轴。在 MySQL 中，您可以使用带有 CASE 表达式的聚合函数将数据转换为列:

select a.*, u.*,
    SUM(case when v.type='upvote' then 1 else 0 end) Upvotes,
    SUM(case when v.type='downvote' then 1 else 0 end) Downvotes,
from answers a
left join users u using(user_id)
left join vote_history v using(answer_id)
where a.question_id = 4 and deleted < 4
group by v.question_id
order by votes desc