mysql - 在同一个 mysql 表上使用内连接从 select 获取数据

Question

这是我的案例表

id empid    reaction         date_t
1  emp109   interested       2017-09-12
2  emp109   not_interested   2017-09-13
3  emp109   maybe            2017-09-13

我想要实现的是针对特定员工 ID

date_t  | number of interested  |number of not interested   | number of maybe

就像 2017-09-12 和 2017-09-13 的员工 ID emp109 一样，表格将如下所示

 date_t     | number of interested  |number of not interested   | maybe
 2017-09-12    1                        0                        0
 2017-09-13    0                       1                         1

这是我迄今为止尝试过的

SELECT * FROM
(SELECT cases.date_t, cases.empid,COUNT(cases.reaction) as interested  FROM `cases` WHERE cases.reaction="interested" and cases.empid="EMP12654"  GROUP by cases.date_t)alpha
JOIN
(SELECT cases.date_t, cases.empid,COUNT(cases.reaction) as not_interested  FROM `cases` WHERE cases.reaction="not_interested" and cases.empid="EMP12654"  GROUP by cases.date_t)beta
on alpha.date_t=beta.date_t
JOIN
(SELECT cases.date_t, cases.empid,COUNT(cases.reaction) as maybe  FROM `cases` WHERE cases.reaction="maybe" and cases.empid="EMP12654"  GROUP by cases.date_t)zeta
on alpha.date_t=zeta.date_t

这并没有给我想要的结果，我不知道如何实现它？

Answer 1

这称为旋转。

您可以使用条件聚合在 MySQL 中执行此操作:

select date_t,
    sum(reaction = 'interested') as interested,
    sum(reaction = 'not_interested') as not_interested,
    sum(reaction = 'maybe') as maybe
from your_table
group by date_t;

上面使用了 MySQL 将 true 视为 1，将 false 视为 0 的事实。