php - 获取一周中的每一天作为列 MySQL 和 PHP

Question

我正在用 PHP 和 MySQL 编写 Web 应用程序。

我有一个名为 counts 的表，数据是这样存储在该表中的:

Table: countsid   counts   location_id   media_id   created_at--------------------------------------------------1     50         1             1        2017-03-152     30         2             1        2017-03-153     80         1             2        2017-03-154     20         1             1        2017-03-165     100        2             2        2017-03-16

对于每个唯一的 location_id、media_id 和 created_at，我存储计数。

我有另一个表位置是这样的:

Table: locationsid       name----------------1     Location 12     Location 23     Location 34     Location 45     Location 5

这是我目前的 SQL 查询:

select sum(counts.count) as views, locations.name as locations, DAYNAME(counts.created_at) AS weekday from `counts` inner join `locations` on `locations`.`id` = `counts`.`location_id` where `counts`.`created_at` between '2016-12-04' and '2016-12-10' group by `weekday`, `counts`.`location_id`;

数据是这样显示的:

locations     weekday     views-----------------------------------Location 1    Mon          50Location 1    Tue          30Location 2    Mon          20Location 2    Tue          70

我正在创建报告，我想运行一个查询，使一周中的所有日子都显示为一列，其相应的值作为一周中那一天的查看次数。我想要这样的东西:

locations     mon   tue  wed  thu  fri  sat   sun-------------------------------------------------Location 1    40    60   51   20   40   20    30Location 2    80    60   100  24   30   10    5  

上述是否可以在 MySQL 中实现，或者我必须使用 PHP 才能实现？如果是这样，我该怎么做？

任何帮助将不胜感激，谢谢。

注意:示例数据不准确。

Answer 1

通过使用条件聚合，MySQL 可以实现这一结果。

诀窍是在 SELECT 列表中的表达式中使用条件测试来确定是否返回计数值。

像这样:

 SELECT l.name                                                  AS `locations`
      , SUM(IF(DATE_FORMAT(c.created_at,'%a')='Mon',c.count,0)) AS `mon`
      , SUM(IF(DATE_FORMAT(c.created_at,'%a')='Tue',c.count,0)) AS `tue`
      , SUM(IF(DATE_FORMAT(c.created_at,'%a')='Wed',c.count,0)) AS `wed`
      , SUM(IF(DATE_FORMAT(c.created_at,'%a')='Thu',c.count,0)) AS `thu`
      , SUM(IF(DATE_FORMAT(c.created_at,'%a')='Fri',c.count,0)) AS `fri`
      , SUM(IF(DATE_FORMAT(c.created_at,'%a')='Sat',c.count,0)) AS `sat`
      , SUM(IF(DATE_FORMAT(c.created_at,'%a')='Sun',c.count,0)) AS `sun`
   FROM `locations` l
   LEFT
   JOIN `counts` c
     ON c.location_id = l.id
    AND c.created_at >= '2016-12-04'
    AND c.created_at  < '2016-12-04' + INTERVAL 7 DAY 
  GROUP BY l.name
  ORDER BY l.name

注意:

示例数据中有两行 location_id=1 and created_at='2016-03-15' ，因此此查询将为 tue 返回总共 130 个(=50+80)，而不是 50(如现有查询的示例输出所示)。