select a.orderno,total1,total2 from (select orderno,total1=sum(orderqty) from @tOrder group by orderno) a join (select orderno,total2=sum(outputqty) from @tProd group by orderno) b on a.orderno=b.orderno
declare @tOrder table(orderno nvarchar(2),orderqty int) Insert @tOrder select N'01',3000 union all select N'01',1200 union all select N'01',4500 declare @tProd table(orderno nvarchar(2),outputqty int) Insert @tProd select N'01',500 union all select N'01',300 union all select N'01',800 SELECT a.orderno,a.orderqty,b.outputqty FROM ( SELECT orderno,SUM(orderqty) AS orderqty FROM @tOrder GROUP BY orderno ) AS a LEFT OUTER JOIN ( SELECT orderno,SUM(outputqty) AS outputqty FROM @tProd GROUP BY orderno ) AS b ON a.orderno=b.orderno/* orderno orderqty outputqty ------- ----------- ----------- 01 8700 1600(1 行受影响)*/
select a.orderno,orderqty,outputqty from @tOrder a,@tProd b where a.orderno=b.orderno 这样查一下就知道为什么不正确了
declare @tOrder table(orderno nvarchar(2),orderqty int) Insert @tOrder select N'01',3000 union all select N'01',1200 union all select N'01',4500 declare @tProd table(orderno nvarchar(2),outputqty int) Insert @tProd select N'01',500 union all select N'01',300 union all select N'01',800 select o.orderno,o.orderqty,p.outputqty from (select orderno ,sum(orderqty )orderqty from @tOrder group by orderno) o inner join (select orderno ,sum(outputqty )outputqty from @tProd group by orderno) p on o.orderno=p.orderno
Insert @tOrder
select N'01',3000 union all
select N'01',1200 union all
select N'01',4500 declare @tProd table(orderno nvarchar(2),outputqty int)
Insert @tProd
select N'01',500 union all
select N'01',300 union all
select N'01',800 SELECT a.orderno,a.orderqty,b.outputqty
FROM
(
SELECT orderno,SUM(orderqty) AS orderqty
FROM @tOrder
GROUP BY orderno
) AS a
LEFT OUTER JOIN
(
SELECT orderno,SUM(outputqty) AS outputqty
FROM @tProd
GROUP BY orderno
) AS b
ON a.orderno=b.orderno/*
orderno orderqty outputqty
------- ----------- -----------
01 8700 1600(1 行受影响)*/
select a.orderno,orderqty,outputqty from @tOrder a,@tProd b where a.orderno=b.orderno
这样查一下就知道为什么不正确了
Insert @tOrder
select N'01',3000 union all
select N'01',1200 union all
select N'01',4500
declare @tProd table(orderno nvarchar(2),outputqty int)
Insert @tProd
select N'01',500 union all
select N'01',300 union all
select N'01',800
select o.orderno,o.orderqty,p.outputqty from (select orderno ,sum(orderqty )orderqty from @tOrder group by orderno) o
inner join (select orderno ,sum(outputqty )outputqty from @tProd group by orderno) p on o.orderno=p.orderno