我有一个名为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来实现这一点？如果是这样,我该怎么做呢？
任何帮助将不胜感激,谢谢.
注意：样本数据不准确.
最佳答案
使用条件聚合可以使用MysqL实现此结果.
诀窍是在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(如示例输出中所示)现有的查询).