php - 当所有数据都在一个表中时，MySQL连接多个sql结果

Question

我有一个包含大量数据的表格，其中包含多个字段:

day (Monday ~ Sunday)
City (NY, LA, FL, etc.)

我想用 php 制作一个表格来显示这样的总数:

  --- Mon --- Tue --- Wed ...
NY    6       8       9
LA    7       5       3
FL    1       0       4 

但是我在编写 SQL 来完成这项工作时遇到了问题。我使用过 union 但它在底部附加了结果，所以我似乎需要使用 inner join，或者甚至不需要。我可以远程接近我想要的两个查询是:

select city, count(id) as 'Monday' from <code>index</code> where city = "NY" and day = 'Monday' group by city
union all <br/>select city, count(id) as 'Monday' from <code>index</code> where city = 'LA' and day = 'Monday' group by city
union all <br/>select city, count(id) as 'Monday' from <code>index</code> where city = 'FL' and day = 'Monday' group by city


这会产生以下结果

  --- Mon
NY    6
LA     7
FL     1

问题是我不知道如何将剩余的日子添加到它的右边

我试过的另一个查询是
select a.day, a.counts as 'NY', b.counts as 'LA' from <br/>(select day, count(id) as counts from <code>index</code> where city='NY' GROUP BY DAY) as a <br/>left join <br/>(select day, count(id) as counts from <code>index</code> where city='LA' GROUP BY DAY) as b <br/>on (a.day = b.day) <br/>order by field (a.<code>day</code>, 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday')

这会产生以下结果

                 NY --- LA ---
Monday      6        7
Tuesday     8        5

这个的问题是应该交换行和列，而且我不知道如何添加另一个城市(我有很多城市)。

所有数据都在一个名为“索引”的表中。有人可以指导我如何编写 SQL 吗？当没有记录时，我还需要该字段显示 0 或 Null。最终目标是用 PHP 生成表格。

感谢您花时间阅读和回答。

Answer 1

您似乎想要按 day 对数据进行透视，并按 City 对数据进行分组。如果是这样，那么您可以尝试以下查询可能会有所帮助。

select city, 
sum(case when (day = 'Monday') then  1 else 0 end)  'Monday',
sum(case when (day = 'Tuesday') then  1 else 0 end)  'Tuesday',
sum(case when (day = 'Wednesday') then  1 else 0 end)  'Wednesday',
sum(case when (day = 'Thursday') then  1 else 0 end)  'Thursday',
sum(case when (day = 'Friday') then  1 else 0 end)  'Friday',
sum(case when (day = 'Saturday') then  1 else 0 end)  'Saturday',
 sum(case when (day = 'Sunday') then  1 else 0 end)  'Sunday'

from `index` group by city