如何将CURDATE()作为列名使用？附失效SQL查询示例

嘿，我明白你遇到的问题了——你想把CURDATE()、CURDATE()-1这类表达式直接当作查询结果的列别名，但SQL语句死活跑不起来，对吧？这其实是SQL列别名的语法规则在限制你，咱们一步步来解决：

问题根源 #

SQL里的列别名如果包含特殊字符（比如括号()、减号-），或者本身是一个函数/表达式的话，不能直接裸写。数据库会把CURDATE()-1这种写法当成数学表达式去计算，而不是把它当作一个整体的列名，自然就会报错。

解决方案：给特殊别名加引号包裹 #

不同数据库对特殊别名的包裹方式略有不同，下面给你对应主流数据库的修正写法：

1. MySQL/MariaDB 版本

用反引号把别名括起来，这样数据库就会把它当作完整的列名：

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`
FROM your_table; -- 记得替换成你的实际表名

2. PostgreSQL 版本

PostgreSQL用双引号包裹特殊别名，另外日期运算要改成INTERVAL语法：

SELECT 
  SUM(last_access_date > CURRENT_DATE AND last_access_date < CURRENT_DATE + INTERVAL '1 day') AS "CURDATE()",
  SUM(last_access_date > CURRENT_DATE - INTERVAL '1 day' AND last_access_date < CURRENT_DATE) AS "CURDATE()-1",
  SUM(last_access_date > CURRENT_DATE - INTERVAL '2 days' AND last_access_date < CURRENT_DATE - INTERVAL '1 day') AS "CURDATE()-2",
  SUM(last_access_date > CURRENT_DATE - INTERVAL '3 days' AND last_access_date < CURRENT_DATE - INTERVAL '2 days') AS "CURDATE()-3",
  SUM(last_access_date > CURRENT_DATE - INTERVAL '4 days' AND last_access_date < CURRENT_DATE - INTERVAL '3 days') AS "CURDATE()-4"
FROM your_table;

3. SQL Server 版本

SQL Server用方括号包裹特殊别名，同时日期函数和运算要调整：

SELECT 
  SUM(CASE WHEN last_access_date > CAST(GETDATE() AS DATE) AND last_access_date < DATEADD(day, 1, CAST(GETDATE() AS DATE)) THEN 1 ELSE 0 END) AS [CURDATE()],
  SUM(CASE WHEN last_access_date > DATEADD(day, -1, CAST(GETDATE() AS DATE)) AND last_access_date < CAST(GETDATE() AS DATE) THEN 1 ELSE 0 END) AS [CURDATE()-1],
  SUM(CASE WHEN last_access_date > DATEADD(day, -2, CAST(GETDATE() AS DATE)) AND last_access_date < DATEADD(day, -1, CAST(GETDATE() AS DATE)) THEN 1 ELSE 0 END) AS [CURDATE()-2],
  SUM(CASE WHEN last_access_date > DATEADD(day, -3, CAST(GETDATE() AS DATE)) AND last_access_date < DATEADD(day, -2, CAST(GETDATE() AS DATE)) THEN 1 ELSE 0 END) AS [CURDATE()-3],
  SUM(CASE WHEN last_access_date > DATEADD(day, -4, CAST(GETDATE() AS DATE)) AND last_access_date < DATEADD(day, -3, CAST(GETDATE() AS DATE)) THEN 1 ELSE 0 END) AS [CURDATE()-4]
FROM your_table;

额外小建议 #

其实你可以给列别名起更易读的名字，比如今日访问量、昨日访问量、前天访问量，这样后续看结果或者维护代码的时候会更方便，不用再去解析表达式含义~

内容的提问来源于stack exchange，提问作者kyun