累加的SQL指令
求一个累加的SQL指令
有两张表 ,一样是订单表 另一个是 出货单表 , 出货单表的OrderID 字段关联的是订单表ID
现在的需求是这样的 列出每个订单的发货明细,并每个出货单后要加一项累计出货数据(非数据库字段),比如订单的第二笔发货的累计出货是1,2两个出货单的和,第三笔为 1,2,3的和,依次类推,请高手出招
------解决思路----------------------
------解决思路----------------------
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),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
------解决思路----------------------
列出示例数据和想要的结果,这样比较容易看出楼主想要的是什么效果,并且说明一下sqlserver的版本
------解决思路----------------------
有两张表 ,一样是订单表 另一个是 出货单表 , 出货单表的OrderID 字段关联的是订单表ID
现在的需求是这样的 列出每个订单的发货明细,并每个出货单后要加一项累计出货数据(非数据库字段),比如订单的第二笔发货的累计出货是1,2两个出货单的和,第三笔为 1,2,3的和,依次类推,请高手出招
------解决思路----------------------
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
------解决思路----------------------
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),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
------解决思路----------------------
列出示例数据和想要的结果,这样比较容易看出楼主想要的是什么效果,并且说明一下sqlserver的版本
------解决思路----------------------
----------------------------------你要的结果是这样的吗?
--发货表(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