SQL选择虚拟列引用虚拟列
SQL选择虚拟列引用虚拟列
提问于 2018-03-28 19:07:48
我甚至不知道如何正确地表述我的问题，但基本上我在Azure SQL Server (Transact-SQL)中有这样的Select语句：
--这不起作用：
SELECT
Person.PersonID,
Person.DisplayName,
VacationRequest.VacationYear,
SUM(code for VacApp) AS VacApp,
SUM(code for VacPen) AS VacPen,
(VacApp + VacPen) AS VacBal
FROM Person
INNER JOIN VacationRequest ON Person.PersonID = VacationRequest.PersonID
GROUP BY Person.PersonID,Person.DisplayName, VacationRequest.VacationYear;除了我得到的最后一列VacBal错误之外，一切正常：
"Failed to execute query. Error: Invalid column name 'VacApp'. Invalid column name 'VacPen'." 如果我将语句转换为不使用VacApp和VacPen，并将它们的求和函数放在它们的位置上，它就能工作。但是，SQL代码会变得混乱，因为我的SUM()函数很长而且很复杂。
这工作：
SELECT
Person.PersonID,
Person.DisplayName,
VacationRequest.VacationYear,
SUM(code for VacApp) AS VacApp,
SUM(code for VacPen) AS VacPen,
(SUM(code for VacApp) + SUM(code for VacPen)) AS VacBal
FROM Person
INNER JOIN VacationRequest ON Person.PersonID = VacationRequest.PersonID
GROUP BY Person.PersonID,Person.DisplayName, VacationRequest.VacationYear;我可以用这个，但是几年后我在这里到底做了什么，这将是很难理解的。另外，如果我为VacApp切换SUM函数，我需要记住复制粘贴到VacBal中。
有没有一种方法可以简化这一点，比如使用(VacApp + VacPen)作为VacBal？
另外，这些列是如何在SELECT语句中创建的(使用SUM之类的函数创建的列，或者当它们将几个列合并成一个列时)？我称它们为虚拟列，但我确信这是不正确的命名。
回答 2
Stack Overflow用户
发布于 2018-03-28 19:11:23
如果要添加值，则需要一个子查询或CTE：
SELECT vpr.*, (VacApp + VacPen) AS VacBal
FROM (SELECT p.PersonID, p.DisplayName, vr.VacationYear,
SUM(code for VacApp) AS VacApp,
SUM(code for VacPen) AS VacPen
FROM Person p JOIN
VacationRequest vr
ON p.PersonID = vr.PersonID
GROUP BY p.PersonID, p.DisplayName, vr.VacationYear
) vrp;注意，表别名使查询更易于编写和读取。
Stack Overflow用户
发布于 2018-03-28 19:09:28
您可以使用subquery/CTE:：
WITH cte AS (
SELECT
Person.PersonID,
Person.DisplayName,
VacationRequest.VacationYear,
SUM(code for VacApp) AS VacApp,
SUM(code for VacPen) AS VacPen
FROM Person
INNER JOIN VacationRequest ON Person.PersonID = VacationRequest.PersonID
GROUP BY Person.PersonID,Person.DisplayName, VacationRequest.VacationYear
)
SELECT *, (VacApp + VacPen) AS VacBal
FROM cte;或使用交叉/外部应用：
SELECT
Person.PersonID,
Person.DisplayName,
VacationRequest.VacationYear,
SUM(s1.col_name) AS VacApp,
SUM(s2.col_name) AS VacPen,
(SUM(s1.col_name) + SUM(s2.col_name)) AS VacBal
FROM Person
INNER JOIN VacationRequest ON Person.PersonID = VacationRequest.PersonID
OUTER APPLY (code for VAcApp) s1
OUTER APPLY (code for VacPen) s2
GROUP BY Person.PersonID,Person.DisplayName, VacationRequest.VacationYear;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/49542346
复制相关文章
相似问题
腾讯云开发者
