问题描述
我有 Product 表,其中包含 QtyID、Qty 和 Year_ID 列.我还有一个表 tbl_Years 带有 ID 和 Year.
I have Product table with columns QtyID, Qty and Year_ID. I also have a table tbl_Years with ID and Year.
我有一个简单的 SELECT 语句,其中 SUM 计算 Qty:
I have a simple SELECT statement with SUM calculation of Qty:
SELECT SUM(Qty) AS /*Sum_of_year_2016*/ FROM Product p INNER JOIN tbl_Years ty ON p.Year_ID = ty.Year_ID WHERE ty.Year_ID = 6;
我想为 SUM(Qty) 值定义别名 Sum_of_year_2016.
I want to define an alias name of Sum_of_year_2016 for the SUM(Qty) value.
注意:应从 tbl_Years 表中获取年份.
Note: the year should be fetched from the tbl_Years table.
我的尝试:
SELECT SUM(Qty) AS 'Sum_of_year_' + ty.Year FROM Product p INNER JOIN tbl_Years ty ON p.Year_ID = ty.Year_ID WHERE ty.Year_ID = 6;
但我收到一个错误:
语法错误;'+' 附近的语法不正确.
Syntax error; incorrect syntax near '+'.
推荐答案
需要使用动态SQL获取自定义别名:
You need to use dynamic SQL to get custom alias:
DECLARE @year NVARCHAR(4) = (SELECT TOP 1 Year FROM tbl_Years WHERE Year_id=6); DECLARE @sql NVARCHAR(MAX) = 'SELECT SUM(Qty) AS ' + QUOTENAME('Sum_of_year_' + @year) + ' FROM Product p INNER JOIN tbl_Years ty ON p.Year_ID = ty.Year_ID Where ty.Year_ID = 6;'; EXEC sp_executesql @sql;