我的数据库使用mysql。我在表格中创建了分析细节。我想在图表中显示这些数据。我已经创建了点击特定链接的数据和保存在数据库中的日期时间。表格类似
id | datetime
-------------
1 | 2016-04-18 05:26:36
2 | 2016-04-18 07:26:36
3 | 2016-04-18 09:26:36
4 | 2016-04-18 11:26:36
5 | 2016-04-18 08:26:36
6 | 2016-04-18 05:26:36
7 | 2016-04-18 20:26:36
8 | 2016-04-18 18:26:36
我想像一样显示今天基于小时的数据
hour | click
---------------
1 | 0
2 | 0
3 | 0
4 | 0
5 | 2
6 | 0
7 | 1
8 | 1
9 | 1
10 | 0
11 | 1
12 | 0
13 | 0
14 | 0
15 | 0
16 | 0
17 | 0
18 | 1
19 | 0
20 | 1
21 | 0
22 | 0
23 | 0
24 | 0
同样，对于每周，工作日（周日、周一、周二…周六）而不是小时，周数（1,2,3,4,5…28/29/30/31）用于每月详细信息。
我对今天列表使用了一个查询，但它只给出数据所在的小时名称。如果该小时没有值，我需要填写0的所有小时列表。
我正在使用此查询。
SELECT DISTINCT(hour(`beacon_date_time`)) as hour,COUNT(*) from beaconanalytics WHERE DATE_FORMAT(`beacon_date_time`,'%Y-%m-%d') BETWEEN DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 DAY),'%Y-%m-%d') AND DATE_FORMAT(NOW(),'%Y-%m-%d') GROUP by hour(`beacon_date_time`)
我使用了七天的查询，但它也给出了数据库中当天的记录，而不是剩余天数的显示0。
这就是查询。
SELECT dayname(`beacon_date_time`) as hour,COUNT(*) from beaconanalytics WHERE DATE_FORMAT(`beacon_date_time`,'%Y-%m-%d') BETWEEN DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 7 DAY),'%Y-%m-%d') AND DATE_FORMAT(NOW(),'%Y-%m-%d') GROUP by dayname(`beacon_date_time`)
试试这个，看起来有点奇怪；）
SELECT tmp.hour, sum(tmp.click) AS click
FROM
(SELECT DISTINCT
(hour(`beacon_date_time`)) AS hour,
COUNT(*) AS click
FROM beaconanalytics
WHERE DATE_FORMAT(`beacon_date_time`, '%Y-%m-%d') BETWEEN DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 DAY),
'%Y-%m-%d') AND DATE_FORMAT(NOW(), '%Y-%m-%d')
GROUP BY hour(`beacon_date_time`)
UNION
SELECT 1 AS hour, 0 AS click FROM dual
UNION
SELECT 2 AS hour, 0 AS click FROM dual
UNION
SELECT 3 AS hour, 0 AS click FROM dual
UNION
SELECT 4 AS hour, 0 AS click FROM dual
UNION
SELECT 5 AS hour, 0 AS click FROM dual
UNION
SELECT 6 AS hour, 0 AS click FROM dual
UNION
SELECT 7 AS hour, 0 AS click FROM dual
UNION
SELECT 8 AS hour, 0 AS click FROM dual
UNION
SELECT 9 AS hour, 0 AS click FROM dual
UNION
SELECT 10 AS hour, 0 AS click FROM dual
UNION
SELECT 11 AS hour, 0 AS click FROM dual
UNION
SELECT 12 AS hour, 0 AS click FROM dual
UNION
SELECT 13 AS hour, 0 AS click FROM dual
UNION
SELECT 14 AS hour, 0 AS click FROM dual
UNION
SELECT 15 AS hour, 0 AS click FROM dual
UNION
SELECT 16 AS hour, 0 AS click FROM dual
UNION
SELECT 17 AS hour, 0 AS click FROM dual
UNION
SELECT 18 AS hour, 0 AS click FROM dual
UNION
SELECT 19 AS hour, 0 AS click FROM dual
UNION
SELECT 20 AS hour, 0 AS click FROM dual
UNION
SELECT 21 AS hour, 0 AS click FROM dual
UNION
SELECT 22 AS hour, 0 AS click FROM dual
UNION
SELECT 23 AS hour, 0 AS click FROM dual
UNION
SELECT 24 AS hour, 0 AS click FROM dual) tmp
GROUP BY tmp.hour
每周试试这个；）
SELECT tmp.dayname, sum(tmp.click) AS click
FROM
(SELECT dayname(`beacon_date_time`) as dayname,COUNT(*) AS click from beaconanalytics WHERE DATE_FORMAT(`beacon_date_time`,'%Y-%m-%d') BETWEEN DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 7 DAY),'%Y-%m-%d') AND DATE_FORMAT(NOW(),'%Y-%m-%d') GROUP by dayname(`beacon_date_time`)
UNION
SELECT 'Monday' AS dayname, 0 AS click FROM dual
UNION
SELECT 'Tuesday' AS dayname, 0 AS click FROM dual
UNION
SELECT 'Wednesday' AS dayname, 0 AS click FROM dual
UNION
SELECT 'Thursday' AS dayname, 0 AS click FROM dual
UNION
SELECT 'Friday' AS dayname, 0 AS click FROM dual
UNION
SELECT 'Saturday' AS dayname, 0 AS click FROM dual
UNION
SELECT 'Sunday' AS dayname, 0 AS click FROM dual
) tmp
GROUP BY tmp.dayname
请尝试
SELECT days.daynum, IF(count.c IS NULL, 0, count.c) from
(
SELECT t*10+u daynum
FROM
(SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) A,
(SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) B
ORDER BY daynum
) as days
LEFT JOIN
(
SELECT DISTINCT(hour(`beacon_date_time`)) as hour,COUNT(*) as c from beaconanalytics WHERE DATE_FORMAT(`beacon_date_time`,'%Y-%m-%d') BETWEEN DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 DAY),'%Y-%m-%d') AND DATE_FORMAT(NOW(),'%Y-%m-%d') GROUP by hour(`beacon_date_time`)
) as count ON count.hour = days.daynum ORDER BY days.daynum