本文介绍了SQL Server 2008 R2:将别名与列值连接起来的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着跟版网的小编来一起学习吧！
问题描述
我有 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;
这篇关于SQL Server 2008 R2:将别名与列值连接起来的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！