我有以下架构。
表票
+------------------+--------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------------------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| aid | varchar(10) | NO | | | |
| ip | varchar(100) | NO | | | |
| host | varchar(200) | NO | | | |
| timestamp | varchar(20) | NO | | 0000-00-00 00:00:00 | |
| user | tinytext | NO | | NULL | |
| userid | int(10) | NO | | 0 | |
+------------------+--------------+------+-----+---------------------+----------------+
在这里，我想获取过去 7 天内每一天的每项援助的计数，其中“0”表示没有投票支持援助的日期。这里的时间戳是unix时间戳。
非常感谢任何帮助。
MySQL 没有递归功能，因此您只能使用 NUMBERS 表技巧 -
-
创建一个仅保存递增数字的表 - 使用 auto_increment 很容易做到：
DROP TABLE IF EXISTS `example`.`numbers`;
CREATE TABLE `example`.`numbers` (
`id` int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-
使用以下命令填充表：
INSERT INTO NUMBERS
(id)
VALUES
(NULL)
...您需要的任意多个值。
-
Use DATE_ADD构建日期列表，根据 NUMBERS.id 值增加天数。将“2010-01-01”和“2010-01-02”替换为各自的开始日期和结束日期（但使用相同的格式，YYYY-MM-DD HH:MM:SS）。在此示例中，我从 CURRENT_DATE 中减去 NUMBERS.id 值，以获取上周的连续日期值列表 -
SELECT x.dt
FROM (SELECT DATE_SUB(CURRENT_DATE, INTERVAL (n.id - 1) DAY) AS dt
FROM numbers n
WHERE n.id <= 7 ) x
-
根据日期时间部分左连接到数据表。
SELECT x.dt,
COUNT(v.aid) AS num
FROM (SELECT DATE_SUB(CURRENT_DATE, INTERVAL (n.id - 1) DAY) AS dt
FROM numbers n
WHERE n.id <= 7 ) x
LEFT JOIN VOTES v ON DATE(FROM_UNIXTIME(v.timestamp)) = DATE(x.dt)
GROUP BY x.dt
ORDER BY x.dt
为什么是数字而不是日期？
简单 - 可以根据数字生成日期，就像我提供的示例一样。它还意味着使用单个表，而不是每种数据类型一个表。
之前：
SELECT DATE(FROM_UNIXTIME(v.timestamp)) AS dt,
COUNT(v.aid)
FROM VOTES v
WHERE DATE(FROM_UNIXTIME(v.timestamp)) BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
AND CURRENT_DATE
GROUP BY DATE(FROM_UNIXTIME(v.timestamp))
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
