这么一张表:
ID 重量 列名
A1 100
B2 200
C3 300
D4 400
E5 500其中有个起始值:起始重量:1000现在要求一个查询得出:
ID 重量 累计减少重量
A1 100 1000-100
B2 200 1000-100-200
C3 300 1000-100-200-300
D4 400 1000-100-200-300-400
E5 500 1000-100-200-300-400-500我写了个SQL:如下
declare @FirstWeight int = 1000;with Weight as
(
select
'A1' as ID
,100 as Weight
union all
select
'B2' as ID
,200 as Weight
union all
select
'C3' as ID
,300 as Weight
union all
select
'D4' as ID
,400 as Weight
union all
select
'E5' as ID
,500 as Weight
)select
a.ID
,a.Weight
,@FirstWeight
- (select SUM(Weight) from Weight where ID <= a.ID) as RemainAmount
from
Weight as a
但是这个语句在数据量大一点的表中就没办法了,太慢了。。求大虾们搞个快点的Sql感恩啊
ID 重量 列名
A1 100
B2 200
C3 300
D4 400
E5 500其中有个起始值:起始重量:1000现在要求一个查询得出:
ID 重量 累计减少重量
A1 100 1000-100
B2 200 1000-100-200
C3 300 1000-100-200-300
D4 400 1000-100-200-300-400
E5 500 1000-100-200-300-400-500我写了个SQL:如下
declare @FirstWeight int = 1000;with Weight as
(
select
'A1' as ID
,100 as Weight
union all
select
'B2' as ID
,200 as Weight
union all
select
'C3' as ID
,300 as Weight
union all
select
'D4' as ID
,400 as Weight
union all
select
'E5' as ID
,500 as Weight
)select
a.ID
,a.Weight
,@FirstWeight
- (select SUM(Weight) from Weight where ID <= a.ID) as RemainAmount
from
Weight as a
但是这个语句在数据量大一点的表中就没办法了,太慢了。。求大虾们搞个快点的Sql感恩啊
INSERT @TB
SELECT 'A1', 100 UNION ALL
SELECT 'B2', 200 UNION ALL
SELECT 'C3', 300 UNION ALL
SELECT 'D4', 400 UNION ALL
SELECT 'E5', 500declare @FirstWeight int
SET @FirstWeight=1000;WITH CTE AS
(
SELECT *,ROW_NUMBER() OVER (ORDER BY ID) SEQ FROM @TB
)
,CTE2 AS
(
SELECT *,@FirstWeight-W AS BAL FROM CTE WHERE SEQ=1
UNION ALL
SELECT T.*,BAL-T.W FROM CTE AS T, CTE2 AS T2 WHERE T.SEQ=T2.SEQ+1
)
SELECT ID,W,BAL FROM CTE2/*
ID W BAL
---- ----------- -----------
A1 100 900
B2 200 700
C3 300 400
D4 400 0
E5 500 -500
*/
goinsert into #t
select 'a1',100,'' union all
select 'b2',200,'' union all
select 'c3',300,'' union all
select 'd4',400,'' union all
select 'e5',500,'' declare @fist_weight int
declare @weight int
declare @col varchar(200)--利用可更新游标解决。简单,快速,可用于任何版本的sqlserver
declare cr cursor for select weight from #t for update
set @fist_weight = 1000open cr
fetch cr into @weightwhile @@fetch_status=0
begin
set @fist_weight = @fist_weight -@weight
update #t set col=@fist_weight where current of cr
fetch cr into @weight
end
close cr
deallocate cr
select * from #t
drop table #t
if object_id('[tb]') is not null drop table [tb]
create table [tb]([ID] varchar(2),[重量] int,[列名] int)
insert [tb]
select 'A1',100,null union all
select 'B2',200,null union all
select 'C3',300,null union all
select 'D4',400,null union all
select 'E5',500,nulldeclare @nums int
select @nums=1000
update [tb]
set [列名]=@nums,
@nums = @nums-[重量]select * from [tb]
ID 重量 列名
A1 100
B2 200
C3 300
D4 400
E5 500
create table Weight(ID varchar(4), 重量 int)
insert into Weight(Id,重量)
select
'A1',100 union all select
'B2',200 union all select
'C3',300 union all select
'D4',400 union all select
'E5',500;select a.Id, a.重量, 1000-(sum(b.重量)) as 累计减少重量
from Weight a join Weight b on a.id>=b.id
group by a.Id, a.重量
------------------------------------------------------------------
A1 100 900
B2 200 700
C3 300 400
D4 400 0
E5 500 -500