mysql - 动态列别名 [英] mysql - Dynamic column alias

问题描述

我有sql 查询"来查看上周为每个成员完成的工作数量，所以我创建了sql 查询"，它非常有用，但我想从 week1 或第2周到本周的数据我的sql查询"是:

Hi I have "sql query" to see number of the job done for each member in the last week so I create "sql query" and it is work very will but I want to change the alise from week1 or week2 to the data of this week my "sql query" is :

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给了我错误.那么，有没有办法解决这个问题?

and I want to become like this "sql query",but mysql give me error. So, is there anyway to solve the problem?

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 给我这个消息:

sql give me this message:

#1064 - 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册以了解要使用的正确语法'NOW()-INTERVAL 1 WEEK 附近

#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

推荐答案

答案很简单:在 SQL 中是不可能的.列别名是常量.您必须动态创建查询才能实现您想要的:

The answer is simple: It is not possible in SQL. Column aliases are constants. You would have to create the query dynamically to achieve what you want:

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;

这篇关于mysql - 动态列别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！