vb.net 子查询返回了 1 个以上的值。当子查询跟在 =、!= 或子查询用作表达式时，这是不允许的
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26623548/
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
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, or when the subquery is used as an expression
提问by Indrah
i need to update totalfield of tableA with the result from the query select SUM(amt) from SALES group by DATE,RNAME
我需要使用查询的结果更新tableA 的total字段 select SUM(amt) from SALES group by DATE,RNAME
i tried like this
我试过这样
update tableA set Total=(select sum(billamt) from SALES group by DATE,RNAME)
update tableA set Total=(select sum(billamt) from SALES group by DATE,RNAME)
It shows error Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
它显示错误子查询返回超过 1 个值。当子查询跟随 =、!=、<、<=、>、>= 或当子查询用作表达式时，这是不允许的。
回答by BobTheBuilder
The query:
查询：
select sum(billamt) from SALES group by DATE,RNAME
Returns the sumof billamt, grouped by DATE,RNAME.
返回sum的billamt，按分组DATE,RNAME。
If you have more than one DATEor RNAMEunique values, the returned value is a table (and not one field).
如果您有多个DATE或RNAME唯一的值，则返回的值是一张表（而不是一个字段）。
You can run this query alone and check the returned value.
您可以单独运行此查询并检查返回值。
As the error states, you cannot set more than one value to a field.
正如错误所述，您不能为一个字段设置多个值。
回答by anonxen
Try something like:
尝试类似：
update A
set Total = B.Total
From tableA A
Inner Join (select DATE,RNAME,sum(billamt) Total from SALES group by DATE,RNAME) B
On A.Date = B.Date and A.RName = B.RNAme
回答by Imranullah Khan
As the table structure is not clear in the above question, I assume that tableA has column Total, Date and RName
由于上述问题中的表结构不清楚，我假设 tableA 有列 Total、Date 和 RName
update tableA set tableA.total =
a.total from (select SUM(amt) as total, date, rname from SALES group by DATE,RNAME) as a
where a.date = tableA.date and tableA.rname = a.rname
-- Edit for the "case where Rname can be null in sales table" use
-- 编辑“销售表中 Rname 可以为空的情况”使用
update tableA set tableA.total = a.total
from (select SUM(amt) as total, date, rname from #t1 group by DATE,RNAME) as a
where a.date = tableA.date and tableA.rname = a.rname
OR (a.date = tableA.date AND tableA.rname IS NULL and a.rname IS NULL)
-- Further for elaboration
-- 进一步阐述
CREATE TABLE #t1 (amt decimal(18,2), [date] date, rname varchar(50))
INSERT INTO #t1
SELECT 1.9, GETDATE() - 1, NULL
UNION ALL
SELECT 1.9, GETDATE() - 1, NULL
UNION ALL
SELECT 8.9, GETDATE() - 1, NULL
UNION ALL
SELECT 8.9, GETDATE(), NULL
UNION ALL
SELECT 2.9, GETDATE() - 1, 'N1'
UNION ALL
SELECT 2.8, GETDATE() - 1, 'N1'
CREATE TABLE #t2 (total decimal(18,2), [date] date, rname varchar(50))
INSERT INTO #t2
SELECT 0, GETDATE() - 1, NULL
UNION ALL
SELECT 0, GETDATE() - 1, 'N1'
update #t2 set #t2.total = a.total
from (select SUM(amt) as total, date, rname from #t1 group by DATE,RNAME) as a
where a.date = #t2.date and #t2.rname = a.rname
OR (a.date = #t2.date AND #t2.rname IS NULL and a.rname IS NULL)
SELECT * FROM #t2
I hope its clear now
我希望现在清楚
