mysql - 动态列别名

Question

您好，我有“sql 查询”来查看每个成员在上周完成的工作数量，所以我创建了“sql 查询”，它非常有用，但我想更改 week1 的 alise 或week2 到 本周的数据 我的“sql query”是:

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 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOW()-INTERVAL 1 WEEK

Answer 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;