子查询在sqlc中返回了超过1个值#
问题描述:
要在ADO.Net中运行的SQL命令字符串。它可能在子查询中返回了多于一行的问题。子查询在sqlc中返回了超过1个值#
string str = "
select
(select (quantity) from Orderinfo where iron=1 and rno=o.rno) as iron
,(select (quantity) from Orderinfo where wash=1 and rno=o.rno) as wash
,(select (quantity) from Orderinfo where dryclean=1 and rno=o.rno) as dryclean
,o.rno
,o.id
,o.name
,(select sum(convert(int,d.quantity)) from orderinfo as d where d.rno=o.rno) as quantity
,o.status
from orderinfo as o
where o.status='Order in Process'
group by o.rno,o.id ,o.status ,o.name";
答
如果我正确理解你想要的结果,你需要得到量的总和:
string str = "
select
(select sum(quantity) from Orderinfo where iron=1 and rno=o.rno) as iron
,(select sum(quantity) from Orderinfo where wash=1 and rno=o.rno) as wash
,(select sum(quantity) from Orderinfo where dryclean=1 and rno=o.rno) as dryclean
,o.rno
,o.id
,o.name
,(select sum(convert(int,d.quantity)) from orderinfo as d where d.rno=o.rno) as quantity
,o.status
from orderinfo as o
where o.status='Order in Process'
group by o.rno,o.id ,o.status ,o.name";
顺便说一句,我假设它是正确的,你想整个的量“o.rno “独立于具体的”o.id“。否则,子查询不需要。
改为使用select top 1 .......。 –
请小心处理您的SQL查询,它们应该像应用程序代码一样可读,可维护且清晰。 – Arran