求大佬解答复杂联合查询sql则呢写

[佯佯佯]

求大佬解答一下sql语句该怎么写

有三张表 分别是货物表、入库表、出库表

大致如下

货物表 Order

orderid name object
1 苹果 散装
2 香蕉 袋装

入库表 OrderTo

OrderToid orderid number from
1 1 400 北京
2 1 200 天津
3 2 150 山西
4 2 120 陕西

出库表 OrderOut
OrderOutid orderid number from
1 1 50 重庆
2 1 30 上海
3 2 40 广州
4 2 60 云南

想联合查询得到的如下
OrderId Name Object Sum
1 苹果 散装 520
2 香蕉 袋装 70

sum为入库表OrderId相等Number的和 减去 出库表OrderId相等Number的和


求大佬解答一下这个sql语句该怎么写 跪谢

[挑粪水的农民]

select OrderId,Name ,Object ,ISNULL(t1.入库数量,0)-ISNULL(t2.出库数量,0)
from Order
left join(select orderid,sum(number) AS 入库数量 from OrderTo group by orderid ) AS T1 ON Order.orderid=T1.orderid
left join(select orderid,sum(number) AS 出库数量 from OrderOut group by orderid ) AS T2 ON Order.orderid=T2.orderid

[佯佯佯]

引用 1 楼 挑粪水的农民 的回复:

select OrderId,Name ,Object ,ISNULL(t1.入库数量,0)-ISNULL(t2.出库数量,0)
from Order
left join(select orderid,sum(number) AS 入库数量 from OrderTo group by orderid ) AS T1 ON Order.orderid=T1.orderid
left join(select orderid,sum(number) AS 出库数量 from OrderOut group by orderid ) AS T2 ON Order.orderid=T2.orderid

这么写会报错 as附近的语法错误

[挑粪水的农民]

CREATE TABLE Orderdata(orderid INT ,name NVARCHAR(10),Packing NVARCHAR(20))
GO
CREATE TABLE OrderTo(ordertoid INT ,orderid INT ,number INT ,Comefrom NVARCHAR(20))
GO
CREATE TABLE OrderOut(orderoutid INT ,orderid INT ,number INT ,Comefrom NVARCHAR(20))
GO

INSERT INTO Orderdata
SELECT 1,'苹果' ,'散装'
UNION ALL
SELECT 2,'香蕉' ,'袋装'

INSERT INTO OrderTo 
SELECT 1,1,400 ,'北京'  
UNION ALL 
SELECT 2,1,200 ,'天津'
UNION ALL 
SELECT 3,2,150 ,'山西'
UNION ALL 
SELECT 4,2,120 ,'陕西'

INSERT INTO OrderOut 
SELECT 1,1,50 ,'重庆'  
UNION ALL 
SELECT 2,1,30 ,'上海'
UNION ALL 
SELECT 3,2,40 ,'广州'
UNION ALL 
SELECT 4,2,60 ,'云南'


select Orderdata.OrderId,Name ,Packing ,ISNULL(t1.入库数量,0)-ISNULL(t2.出库数量,0)
from Orderdata
left join(select orderid,sum(number) AS 入库数量 from OrderTo group by orderid  ) AS T1 ON Orderdata.orderid=T1.orderid
left join(select orderid,sum(number) AS 出库数量 from OrderOut group by orderid  ) AS T2 ON Orderdata.orderid=T2.orderid