Select o.*,sum_num,cut_num
from dbo.Orders as o
join
(Select s.OrderID,sum(出荷数量) as sum_num,Count(s.OrderID) as cut_num
from dbo.Sales as s
group by s.OrderID
) as d on o.ID=d.OrderID
from dbo.Orders as o
join
(Select s.OrderID,sum(出荷数量) as sum_num,Count(s.OrderID) as cut_num
from dbo.Sales as s
group by s.OrderID
) as d on o.ID=d.OrderID
insert into #DDB(OrderID,CHL)
select 101,102 union all
select 204,204 union all
select 126,127 union all
select 212,211 union all
select 100,100 union all
select 200,200 union all
select 120,120create table #CHB(ID VARCHAR(20),RHL int)
insert into #CHB(ID,RHL)
select 101,105 union all
select 204,207 union all
select 126,154 union all
select 212,222 union all
select 100,111 union all
select 200,202 union all
select 120,157
SELECT A.OrderID,A.CHL,B.RHL
FROM #DDB A JOIN #CHB B
ON A.OrderID=B.ID
UNION ALL
SELECT '总和',SUM(A.CHL),SUM(B.RHL)
FROM #DDB A JOIN #CHB B
ON A.OrderID=B.ID如果ID分开统计就在第二个SELECT后面加GROUP BY A.OrderID
----------------------------------你要的结果是这样的吗?
--发货表(chl 指数量)
create table #DDB(OrderID VARCHAR(20),CHL int)
insert into #DDB(OrderID,CHL)
select 101,102 union all
select 204,204 union all
select 126,127 union all
select 212,211 union all
select 100,100 union all
select 200,200 union all
select 120,120
--订单表
create table #CHB(ID VARCHAR(20))
insert into #CHB(ID)
select 101 union all
select 204 union all
select 126 union all
select 212 union all
select 100 union all
select 200 union all
select 120
go
with cte_1 as
(
select #DDB.*,ROW_NUMBER() over(Order by orderid )id from #DDB inner join #CHB on #ddb.OrderID = #CHB .id
)
select cte_1.OrderID,cte_1.CHL,cte_2.订单累计数量 from cte_1
inner join
(
select a.OrderID,SUM(b.chl) as 订单累计数量 from cte_1 a left join cte_1 b on a.id >= b.id
group by a.OrderID
)cte_2 on cte_1.OrderID = cte_2.OrderID
CHL出货单表 的出货量,RHL订单表的入货量,不知道我理解的对不对
SELECT
T1.*,T2.出货单主键,T2.出货数
,ROW_NUMBER()OVER(PARTITION BY OrderID Order by 出货单主键)RN
FROM 订单表 T1 JOIN 出货单表 T2 ON T1.ID=T2.OrderID
)
SELECT T1.ID,T1.出货单主键,T1.出货数,SUM(T2.出货数)[累计出货数] FROM CTE T1
JOIN CTE T2 ON T1.ID=T2.ID AND T2.RN<=T1.RN
GROUP BY T1.ID,T1.出货单主键,T1.出货数你的出货单表,最好有自己的一个主键,我上面假定叫做出货单主键
或者,有一个给出货单排序的字段也行