7 天前 MySQL Top10

Question

我有一个问题...我在这个网站上搜索任何解决方案...我尝试过它们，但在一次锻炼中:(所以我试图获取 7 天前浏览量排名前 10 的结果...所以我尝试这样的代码:

SELECT * FROM `data` 
WHERE cast(`date` as DATE) BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) 
AND CURDATE() ORDER by `viewed` DESC LIMIT 0,10

或

SELECT * FROM `data` 
WHERE `date` > (NOW() - INTERVAL 7 DAY) 
ORDER by `viewed` DESC LIMIT 0,10

或

SELECT * FROM `data` 
WHERE DATE(`date`) = DATE_SUB(NOW(), INTERVAL 7) 
ORDER by `viewed` DESC LIMIT 0,10

或

SELECT * FROM `data` 
WHERE `date` >= SUBDATE(NOW(), INTERVAL 7 DAY) 
ORDER by `viewed` DESC LIMIT 0,10

我尝试将它们与 NOW() TIME() DATE() CURDATE() 任意组合SUBDATE() SUBTIME() DATE_SUB() 等等...但没有任何效果:(我真的不知道问题是什么。我使用 time() 函数(PHP)将日期提交到数据库，这可能是问题所在吗？

Answer 1

试试这个:

SELECT date, viewed FROM data
WHERE date BETWEEN UNIX_TIMESTAMP(NOW() - INTERVAL 7 day) AND UNIX_TIMESTAMP(NOW())
ORDER BY viewed DESC
LIMIT 0,10;

如果您想要显示日期和时间，则可以这样做。

SELECT FROM_UNIXTIME(date), viewed FROM data
WHERE date BETWEEN UNIX_TIMESTAMP(NOW() - INTERVAL 7 day) AND UNIX_TIMESTAMP(NOW())
ORDER BY viewed DESC
LIMIT 0,10

示例数据:

CREATE TABLE data 
    (
     id int auto_increment primary key, 
     date varchar(10),
     viewed int
    );

INSERT INTO data
(date, viewed)
VALUES
(1392749561, 50),
(1392749950, 25),
(1392850985, 10),
(1393023471, 75),
(1392936840, 100);

SQLFiddle demo