SQL SQLServer 子查询单元作为列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13869959/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQLServer Subquery units as column
提问by yeenow123
I'm trying to get a subquery as a column with a condition.
我正在尝试将子查询作为带有条件的列。
The error I get from SQL Server is:
我从 SQL Server 得到的错误是：
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
子查询返回了 1 个以上的值。当子查询跟随 =、!=、<、<=、>、>= 或当子查询用作表达式时，这是不允许的。
There are a bunch more columns I'm selecting, but here is the main query that is giving ma an error:
我正在选择更多的列，但这是给 ma 一个错误的主要查询：
SELECT ItemNo, Channel, Brand, TotalUnits,
(SELECT TotalUnits FROM Table1 WHERE Locked = 1) As LockedUnits
FROM Table1
EDIT: Locked is a 0 or 1 value
编辑：锁定是 0 或 1 值
Ideally the query would return this (LockedUnits is always a subset of TotalUnits):
理想情况下，查询会返回这个（LockedUnits 总是 TotalUnits 的一个子集）：
ItemNO Channel Brand TotalUnits LockedUnits
1 5 Adidas 100 80
2 7 Nike 500 360
回答by Allan
It looks like what you're trying to do is get TotalUnitsonly for those rows where Locked = 1. If that's the case, you're better off using case:
它看起来就像你正在试图做的是让TotalUnits只为那些行，其中Locked = 1。如果是这种情况，您最好使用case：
SELECT ItemNo, Channel, Brand, TotalUnits,
case when Locked = 1 then TotalUnits else null end As LockedUnits
FROM Table1
You can use a sub-select to do this, but you need to join the sub-select to the main query. Assuming ItemNois unique, it would look like this:
您可以使用子选择来执行此操作，但您需要将子选择加入到主查询中。假设ItemNo是唯一的，它看起来像这样：
SELECT ItemNo, Channel, Brand, TotalUnits,
(SELECT TotalUnits FROM Table1 t2 WHERE Locked = 1
WHERE t1.itemno = t2.itemno) As LockedUnits
FROM Table1 t1
Of course, it's kind of silly to hit a single table twice to get values that are already in the row you're returning.
当然，两次点击单个表以获取您要返回的行中已经存在的值是很愚蠢的。
Comment Response: The only time you'd prefer the sub-select is if you're getting data from a different table or a different row in the same table. And, even then, I prefer to put the table in the where clause and join it to the main table normally.
评论回复：您唯一喜欢子选择的情况是您从不同的表或同一表中的不同行获取数据。而且，即便如此，我还是更喜欢将表放在 where 子句中并正常将其连接到主表。
回答by Zdravko Danev
try putting SELECT sum(TotalUnits) in the subquery
尝试将 SELECT sum(TotalUnits) 放在子查询中
回答by Flat Cat
You have to limit the subquery to a single result, otherwise it cannot be returned in a result set column. You will need to narrow down your results to a single record by SUM(TotalUnits), SELECT TOP 1, or SELECT DISTINCT.
您必须将子查询限制为单个结果，否则无法在结果集列中返回。您需要通过 SUM(TotalUnits)、SELECT TOP 1 或 SELECT DISTINCT 将结果缩小到单个记录。
回答by Jarry Bruckheimer
SELECT ItemNo, Channel, Brand, TotalUnits,
(SELECT sum(TotalUnits) FROM Table1 WHERE Locked = 1) As LockedUnits
FROM Table1
group by ItemNo, Channel, Brand, TotalUnits
