现在有订单表、入库表和出库表，怎样通过一条SQL语句按照订单号分组查询出每个订单号对应的订单数量、入库数量、出库数量和库存数量？

[matitude]

现在有订单表、入库表和出库表，怎样通过一条SQL语句按照订单号分组查询出每个订单号对应的订单数量、入库数量、出库数量和库存数量？
测试用例语句如下：
CREATE TABLE Dingdan(Dingdanhao varchar(12),name varchar(12),guige varchar(10),shuliang int) --订单表
go
insert into Dingdan
select '20170418001','空调','5匹',20 union all
select '20170418002','压缩机','AX',10 union all
select '20170418003','空调','3匹',15 union all
select '20170418004','空调','5匹',30
go
CREATE TABLE Ruku(RukuId int,dingdanhao varchar(12),name varchar(12),guige varchar(10),shuliang int) --入库表
go
insert into Ruku
select 1, '20170418001','空调','5匹',20 union all
select 2, '20170418002','压缩机','AX',5 union all
select 3, '20170418003','空调','3匹',10 union all
select 4, '20170418004','空调','5匹',20 union all
select 5,'20170418002','压缩机','AX',5
go

CREATE TABLE Chuku(ChukuId int,dingdanhao varchar(12),name varchar(12),guige varchar(10),shuliang int ) --出库表
go
insert into Chuku
select 1, '20170418001','空调','5匹',5 union all
select 2, '20170418001','空调','5匹',10 union all
select 3, '20170418002','压缩机','AX',8 union all
select 4, '20170418003','空调','3匹',5 union all
select 5, '20170418003','空调','3匹',5 union all
select 6, '20170418004','空调','5匹',10
go

[二月十六]

SELECT  a.Dingdanhao ,
        a.name ,
        a.guige ,
        ( SELECT    SUM(b.shuliang)
          FROM      dbo.Dingdan b
          WHERE     a.Dingdanhao = b.Dingdanhao
        ) AS DingdanShuliang ,
        ( SELECT    SUM(b.shuliang)
          FROM      dbo.Ruku b
          WHERE     a.Dingdanhao = b.dingdanhao
        ) AS RukuShuliang ,
        ( SELECT    SUM(b.shuliang)
          FROM      dbo.Chuku b
          WHERE     a.Dingdanhao = b.dingdanhao
        ) AS ChukuShuliang
FROM    dbo.Dingdan a;

[matitude]

谢谢！用group by 语句能有更简洁的方法吗？

[二月十六]

;WITH taba AS (
SELECT  dingdanhao ,
        SUM(shuliang) AS RukuShuliang
FROM    dbo.Ruku
GROUP BY dingdanhao
),tabb AS (
SELECT  dingdanhao ,
        SUM(shuliang) AS ChukuShuliang
FROM    dbo.Chuku
GROUP BY dingdanhao
)
SELECT  Dingdan.Dingdanhao ,
        Dingdan.name ,
        Dingdan.guige ,
        SUM(Dingdan.shuliang) AS DingdanShuliang ,
        taba.RukuShuliang ,
        tabb.ChukuShuliang
FROM    dbo.Dingdan
        JOIN taba ON taba.dingdanhao = Dingdan.Dingdanhao
        JOIN tabb ON tabb.dingdanhao = Dingdan.Dingdanhao
GROUP BY Dingdan.Dingdanhao ,
        Dingdan.name ,
        Dingdan.guige ,
        taba.RukuShuliang ,
        tabb.ChukuShuliang