mysql : Select "variable" as "variable2" from table

Question

我在 mySQL 中使用这行代码:

select sum(amount) as 'October_Amount' from table
where month(date)=month(now());

我想获取“Month_Amount”字符串中的当前月份(例如“October_Amount”)。我尝试过这个:

select sum(amount) as monthname(curdate()) from table
where month(date)=month(now());

但是没有成功。 (monthname(now())) 或 (monthname(curdate())) 是字符串，因此它应该可以工作。我尝试了很多版本，但没有找到建议。

Answer 1

如果你想使用动态列名，你可以使用这样的 PREPARED STATMENTS

SET @date = DATE_FORMAT( CURDATE(),'%M');
SET @query = CONCAT('SELECT SUM(amount) AS "', @date, '_Amount" FROM table where MONTH(date) = MONTH(NOW())');
PREPARE dynamic_statement FROM @query;
EXECUTE dynamic_statement;
DEALLOCATE PREPARE dynamic_statement;