主页 / dba / 问题 / 58382
Accepted
LOSTinDB
Asked: 2014-02-07 09:46:12 +0800 CST

如何在查询中使用列的最新日期？

  • 772
SELECT
smart_allusers_karen.lastlogin,
smart_allusers_karen.supemail,
smart_allusers_karen.regionname
FROM
smart_allusers_karen
WHERE lastlogin < MAX(smart_allusers_karen.lastlogin) - INTERVAL 30 DAY AND
      lastlogin > MAX(smart_allusers_karen.lastlogin) - INTERVAL 60 DAY 
ORDER BY lastlogin

尝试获取 MAX 日期时出错。也不确定这是否是最快的方法。

mysql date

2 个回答

  1. Best Answer

    也许您可以拆分查询以在单独的变量中捕获 DateTimes

    SELECT lastlogin INTO @LastLogin FROM smart_allusers_karen ORDER BY lastlogin DESC LIMIT 1;
SET @ThirtyDaysAgo = @LastLogin - INTERVAL 30 DAY;
SET @SixtyDaysAgo  = @LastLogin - INTERVAL 60 DAY;
SELECT
    smart_allusers_karen.lastlogin,
    smart_allusers_karen.supemail,
    smart_allusers_karen.regionname
FROM
    smart_allusers_karen
WHERE lastlogin < @ThirtyDaysAgo  AND
      lastlogin > @SixtyDaysAgo 
ORDER BY lastlogin;

    请确保 lastlogin 已编入索引。如果不是，请先运行：

    ALTER TABLE smart_allusers_karen ADD INDEX (lastlogin);

    试试看 ！！！

    • 2

  2. 不允许以这种方式使用聚合函数，即在WHERE子句中。一种方法是MAX()在子查询中计算：

    SELECT
    k.lastlogin,
    k.supemail,
    k.regionname
FROM
    smart_allusers_karen AS k
WHERE 
    k.lastlogin < (SELECT MAX(lastlogin) FROM smart_allusers_karen) - INTERVAL 30 DAY 
  AND
    k.lastlogin > (SELECT MAX(lastlogin) FROM smart_allusers_karen) - INTERVAL 60 DAY 
ORDER BY
    k.lastlogin ;

    或派生表：

    SELECT
    k.lastlogin,
    k.supemail,
    k.regionname
FROM
    smart_allusers_karen AS k
  CROSS JOIN
    (SELECT MAX(lastlogin) AS lastlogin FROM smart_allusers_karen) AS m
WHERE 
    k.lastlogin < m.lastlogin - INTERVAL 30 DAY 
  AND
    k.lastlogin > m.lastlogin - INTERVAL 60 DAY 
ORDER BY
    k.lastlogin ;
    • 1

相关问题