with tb(a,b,c) as ( select 10,100,0 union all select 11,200,0 union all select 12,50,0 union all select 13,200,0 union all select 14,300,0 union all select 15,200,0 union all select 16,500,0 union all select 17,200,0) select a.a,sum(b.b)/500+1 from tb a,tb b where b.a<=a.a group by a.a列名给你改了,你16行得出的数据不太对吧...
结果有问题,到底是>还是 >=? 计算方法不一样
借用1樓數據。USE test GO with t1(grouptag,shl,pici) as ( select 10,100,0 union all select 11,200,0 union all select 12,50,0 union all select 13,200,0 union all select 14,300,0 union all select 15,200,0 union all select 16,500,0 union all select 17,200,0), t2 AS ( SELECT grouptag,shl,CASE WHEN (SELECT shl FROM t1 WHERE grouptag=(SELECT MIN(grouptag) FROM t1))<500 THEN 1 ELSE 2 END AS pici FROM t1 ) SELECT a.grouptag,a.shl,ISNULL(a.pici+(SUM(b.shl)+c.shl)/500,a.pici) AS pici FROM t2 AS a LEFT JOIN t2 AS b ON a.grouptag>b.grouptag+1 LEFT JOIN t2 AS c ON a.grouptag=c.grouptag+1 GROUP BY a.grouptag,a.shl,a.pici,c.shl /* grouptag shl pici ----------- ----------- ----------- 10 100 1 11 200 1 12 50 1 13 200 1 14 300 2 15 200 2 16 500 3 17 200 4 */
借用下1L数据 with tb(a,b,c) as ( select 10,100,0 union all select 11,200,0 union all select 12,50,0 union all select 13,200,0 union all select 14,300,0 union all select 15,200,0 union all select 16,500,0 union all select 17,200,0) select a.a,a.b,c=(select sum(b.b)/500+1 from tb b where b.a<=a.a) from tb a
USE Test GO
--借用下1L数据;with t1(grouptag,shl,pici) as ( select 10,100,0 union all select 11,200,0 union all select 12,50,0 union all select 13,200,0 union all select 14,300,0 union all select 15,200,0 union all select 16,500,0 union all select 17,200,0 ) select * into dbo.test from t1;declare @i int,@j int; set @i=0 set @j=0update dbo.test set @i=case when @i>=500 then 0 else @i end, @j=case when @i=0 then @j+1 else @j end, @i= @i+shl, pici=@jselect * from dbo.test;grouptag shl pici ----------- ----------- ----------- 10 100 1 11 200 1 12 50 1 13 200 1 14 300 2 15 200 2 16 500 3 17 200 4
with tb(a,b,c) as (
select 10,100,0 union all
select 11,200,0 union all
select 12,50,0 union all
select 13,200,0 union all
select 14,300,0 union all
select 15,200,0 union all
select 16,500,0 union all
select 17,200,0)
select a.a,sum(b.b)/500+1 from tb a,tb b where b.a<=a.a
group by a.a列名给你改了,你16行得出的数据不太对吧...
计算方法不一样
GO
with t1(grouptag,shl,pici) as (
select 10,100,0 union all
select 11,200,0 union all
select 12,50,0 union all
select 13,200,0 union all
select 14,300,0 union all
select 15,200,0 union all
select 16,500,0 union all
select 17,200,0),
t2 AS (
SELECT grouptag,shl,CASE WHEN (SELECT shl FROM t1 WHERE grouptag=(SELECT MIN(grouptag) FROM t1))<500 THEN 1 ELSE 2 END AS pici FROM t1
)
SELECT
a.grouptag,a.shl,ISNULL(a.pici+(SUM(b.shl)+c.shl)/500,a.pici) AS pici
FROM t2 AS a
LEFT JOIN t2 AS b ON a.grouptag>b.grouptag+1
LEFT JOIN t2 AS c ON a.grouptag=c.grouptag+1
GROUP BY a.grouptag,a.shl,a.pici,c.shl
/*
grouptag shl pici
----------- ----------- -----------
10 100 1
11 200 1
12 50 1
13 200 1
14 300 2
15 200 2
16 500 3
17 200 4
*/
with tb(a,b,c) as (
select 10,100,0 union all
select 11,200,0 union all
select 12,50,0 union all
select 13,200,0 union all
select 14,300,0 union all
select 15,200,0 union all
select 16,500,0 union all
select 17,200,0)
select a.a,a.b,c=(select sum(b.b)/500+1 from tb b where b.a<=a.a) from tb a
GO
--借用下1L数据;with t1(grouptag,shl,pici) as
(
select 10,100,0 union all
select 11,200,0 union all
select 12,50,0 union all
select 13,200,0 union all
select 14,300,0 union all
select 15,200,0 union all
select 16,500,0 union all
select 17,200,0
)
select * into dbo.test from t1;declare @i int,@j int;
set @i=0
set @j=0update dbo.test
set @i=case when @i>=500 then 0 else @i end,
@j=case when @i=0 then @j+1 else @j end,
@i= @i+shl,
pici=@jselect * from dbo.test;grouptag shl pici
----------- ----------- -----------
10 100 1
11 200 1
12 50 1
13 200 1
14 300 2
15 200 2
16 500 3
17 200 4