Question

在我的MySQL数据库中,我有一个表格(例如,T),地址和时间(例如,2012-4-30 23:00)。我想抹去本周,但没有上周出现的那些马卡地址。

我怎么能在一句话中这样做? 我试图使用多种选择,但避风港。

提前感谢。

Answer 1

如果没有表格结构,我就建议:

SELECT [DISTINCT] Mac FROM table t
WHERE 
t.Time BETWEEN  2012-01-09 00:00:00  AND  2012-01-16 23:59:59 
AND 
NOT EXISTS (SELECT * FROM FROM table t2 WHERE t2.Mac = t.Mac AND T2.Time BETWEEN  2012-01-01 00:00:00  AND  2012-01-08 23:59:59

Answer 2

您可使用YEARWEEK。

从本周获得记录:

SELECT * FROM T WHERE YEARWEEK(your_datetime_column) = YEARWEEK(NOW());

仅从前几周获得记录:

SELECT * FROM T WHERE YEARWEEK(your_datetime_column) < YEARWEEK(NOW());

Answer 3

为了让本周有时间但上周没有时间的麦克斯讲话,你可以这样做:

SELECT *
FROM T
WHERE YEARWEEK(T.Time) = YEARWEEK(NOW())
AND T.MacAddress NOT IN (
    SELECT MacAddress
    FROM T AS T2
    WHERE YEARWEEK(T2.Time) = YEARWEEK(TIMESTAMPADD(WEEK, -1, NOW())));

使用了YEARWEEK作为bfavaretto的功能已经建议,TIMESTAMPADD/a>,以补充上周的职能。

友情链接