mysql-动态列别名

一尘不染

mysql-动态列别名

sql

您好我有“SQL查询”，看看在上周的每个成员这样做，我创建的“SQL查询”，它是工作很会工作的数量，但我想从ALISE改变 week1或week2
到 的这个数据一周 我的“ SQL查询”是：

SELECT `staffID`,  
 SUM(if (created_at BETWEEN NOW()-INTERVAL 1 WEEK AND NOW(), 1,0)) 
 AS `week1`, 
 SUM(if (created_at BETWEEN NOW()-INTERVAL 2 WEEK AND NOW()-INTERVAL 1 WEEK, 1,0)) 
 AS `week2`, 
FROM tasks 
WHERE status ='done'
GROUP BY `staffID

我想成为这样的“ SQL查询”，但MySQL给我错误。那么，有解决问题的方法吗？

SELECT `staffID`,  
 SUM(if (created_at BETWEEN NOW()-INTERVAL 1 WEEK AND NOW(), 1,0)) 
 AS NOW()-INTERVAL 1 WEEK, 
 SUM(if (created_at BETWEEN NOW()-INTERVAL 2 WEEK AND NOW()-INTERVAL 1 WEEK, 1,0)) 
 AS NOW()-INTERVAL 2 WEEK, 
FROM tasks 
WHERE status ='done'
GROUP BY `staffID

sql给我这个消息：

＃1064-您的SQL语法有误；查看与您的MySQL服务器版本相对应的手册，以在’NOW（）-INTERVAL 1 WEEK附近使用正确的语法

阅读 582

2021-03-17

共1个答案

一尘不染

答案很简单：在SQL中是不可能的。列别名是常量。您必须动态创建查询才能实现所需的功能：

SET @column_alias1 := NOW() - INTERVAL 1 WEEK;
SET @column_alias2 := NOW() - INTERVAL 2 WEEK;
SET @query := CONCAT('SELECT SUM(...) AS `', @column_alias1, '`, SUM(...) AS `', @column_alias2, '` FROM ...');
PREPARE dynamic_statement FROM @query;
EXECUTE dynamic_statement;

2021-03-17