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

我正在用PHP编写PHP的Web应用程序.

我有一个名为count的表,这是数据存储在该表中的方式：

Table: counts

id   counts   location_id   media_id   created_at
--------------------------------------------------
1     50         1             1        2017-03-15
2     30         2             1        2017-03-15
3     80         1             2        2017-03-15
4     20         1             1        2017-03-16
5     100        2             2        2017-03-16

对于每个唯一的location_id,media_id和created_at,我存储计数.

我有另一个表位置,如下所示：

Table: locations

id       name
----------------
1     Location 1
2     Location 2
3     Location 3
4     Location 4
5     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          50
Location 1    Tue          30
Location 2    Mon          20
Location 2    Tue          70

我正在创建报告,我想运行一个查询,以便一周中的所有日子都显示为一列,其对应的值作为一周中该日的查看计数.我想要这样的东西：

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

以上是可能在MySQL或我将不得不使用PHP来实现这一点？如果是这样,我该怎么做呢？

任何帮助将不胜感激,谢谢.

注意：样本数据不准确.

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

像这样的东西：

 SELECT l.name                                                  AS `locations`,SUM(IF(DATE_FORMAT(c.created_at,'%a')='Mon',c.count,0)) AS `mon`,'%a')='Tue',0)) AS `tue`,'%a')='Wed',0)) AS `wed`,'%a')='Thu',0)) AS `thu`,'%a')='Fri',0)) AS `fri`,'%a')='Sat',0)) AS `sat`,'%a')='Sun',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和created_at =’2016-03-15’有两行,因此该查询将返回总计130(对于tue(= 50 80),而不是50(如示例输出中所示)现有的查询).

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!