mysql - 如何将一列变成多列

Question

我有一个关于 mySQL 数据库的非常复杂的 SQL 问题。我先介绍一下这个问题需要的所有表格:

用户表:

Id  date_created
------------------------------
1   2015-09-19T14:18:07.000Z 
2   2015-09-20T01:16:34.000Z
3   2015-09-21T15:10:21.000Z
…

设置表:

Id  User_id     setting_key     setting_value
----------------------------------------------
1   1           city            1
2   3           city            2
3   2           city            1
…

城市名称表:

Id  name
------------------
1   New York    
2   Los Angeles
3   Boston
…

通过选择查询，我想实现这一目标:

date            New York    Los Angeles     Boston  …
------------------------------------------------------
2015-09-19      1           0               0   
2015-09-20      2           0               0
2015-09-21      2           1               0
…

对于特定范围内的每个日期，有多少用户设置为纽约、洛杉矶、洛杉矶......我唯一能实现的就是这个查询:

select date(u.date_created), n.name, count(u.id)
from user u inner join setting s
    on u.id = s.user_id
    and setting_key = 'city'
    inner join name n
    on s.setting_value = n.id
where u.date_created > '2015-09-19T14:18:07.000Z'
group by 1, 2 

但是我得到了这个结果:

date        name        count
-------------------------------
2015-09-19  New York    1
2015-09-19  Los Angeles 0
2015-09-19  Boston      0
2015-09-20  New York    1
2015-09-20  Los Angeles 0
2015-09-20  Boston      0
2015-09-21  New York    0
2015-09-21  Los Angeles 1
2015-09-21  Boston      0
…

它不是累积的，因为每个城市都有行而不是每个城市有列。有人知道(复杂的)答案吗？如果有不清楚的地方，只需询问，在现实生活中，这些表有 50.000 行，我无法更改结构，我需要此查询用于分析目的

Answer 1

如果您知道城市的名称，只需使用条件聚合:

select date(u.date_created), 
       sum(name = 'New York') as NewYork,
       sum(name = 'Los Angeles' as LosAngeles,
       sum(name = 'Boston') as Boston
from user u inner join
     setting s
     on u.id = s.user_id and
        setting_key = 'institution' inner join
     education_niveau en
     on s.setting_value = en.id
where u.date_created > '2015-09-19T14:18:07.000Z'
group by 1;

如果您不知道城市的名称或者有未知的数字，那么您将需要动态 SQL - 即构造 SQL，准备语句，然后执行它。

编辑:

一开始并不清楚问题是关于累积计数的。为此，请使用变量:

select dte,
       (@ny := @ny + NewYork) as NewYork,
       (@la := @la + LosAngeles) as LosAngeles,
       (@b := @b + Boston) as NewYork
from (select date(u.date_created) as dte, 
             sum(name = 'New York') as NewYork,
             sum(name = 'Los Angeles' as LosAngeles,
             sum(name = 'Boston') as Boston
      from user u inner join
           setting s
           on u.id = s.user_id and
              setting_key = 'institution' inner join
           education_niveau en
           on s.setting_value = en.id
      where u.date_created > '2015-09-19T14:18:07.000Z'
      group by 1
      order by 1
     ) us cross join
     (select @ny := 0, @la := 0, @b := 0) params
order by 1;