mysql - 如何使用 CURDATE() 作为列名

Question

这是我的查询(它不起作用。)

SELECT 
  SUM(last_access_date > CURDATE() and last_access_date < CURDATE()+1) as CURDATE(),
  SUM(last_access_date > CURDATE()-1 and last_access_date < CURDATE()) as CURDATE()-1,
  SUM(last_access_date > CURDATE()-2 and last_access_date < CURDATE()-1) as CURDATE()-2,
  SUM(last_access_date > CURDATE()-3 and last_access_date < CURDATE()-2) as CURDATE()-3,
  SUM(last_access_date > CURDATE()-4 and last_access_date < CURDATE()-3) as CURDATE()-4,
  SUM(last_access_date > CURDATE()-5 and last_access_date < CURDATE()-4) as CURDATE()-5,
  SUM(last_access_date > CURDATE()-6 and last_access_date < CURDATE()-5) as CURDATE()-6
FROM MYTABLE

我想看到这样的结果。

Answer 1

这确实是一个要求，最好通过使用 View 技术来解决，例如像 PHP 或者 Java 之类的东西。在这样的应用程序语言中，获取结果集并按照您想要的方式格式化它相对容易。

话虽这么说，如果您需要 MySQL 中的精确输出，这是可能的，但需要相当大的联合查询:

SELECT
    c1, c2, c3, c4, c5, c6, c7
FROM
(
    SELECT 
        DATE_FORMAT(CURDATE(), '%Y-%m-%d') AS c1,
        DATE_FORMAT(CURDATE()-1, '%Y-%m-%d') AS c2,
        DATE_FORMAT(CURDATE()-2, '%Y-%m-%d') AS c3,
        DATE_FORMAT(CURDATE()-3, '%Y-%m-%d') AS c4,
        DATE_FORMAT(CURDATE()-4, '%Y-%m-%d') AS c5,
        DATE_FORMAT(CURDATE()-5, '%Y-%m-%d') AS c6,
        DATE_FORMAT(CURDATE()-6, '%Y-%m-%d') AS c7,
        0 AS position
    FROM dual
    UNION ALL
    SELECT
        (SELECT CAST(SUM(last_access_date > CURDATE() AND last_access_date < CURDATE()+1) AS CHAR(50))),
        (SELECT CAST(SUM(last_access_date > CURDATE()-1 and last_access_date < CURDATE()) AS CHAR(50))),
        (SELECT CAST(SUM(last_access_date > CURDATE()-2 and last_access_date < CURDATE()-1) AS CHAR(50))),
        (SELECT CAST(SUM(last_access_date > CURDATE()-3 and last_access_date < CURDATE()-2) AS CHAR(50))),
        (SELECT CAST(SUM(last_access_date > CURDATE()-4 and last_access_date < CURDATE()-3) AS CHAR(50))),
        (SELECT CAST(SUM(last_access_date > CURDATE()-5 and last_access_date < CURDATE()-4) AS CHAR(50))),
        (SELECT CAST(SUM(last_access_date > CURDATE()-6 and last_access_date < CURDATE()-5) AS CHAR(50))),
        1
    FROM MYTABLE
) t
ORDER BY position;

我面前没有您的确切数据，但我设法使用虚拟总和值设置了一个演示，输出如下所示:

Demo