表
型号 计数
a 10
a 20
a 30
a 10
a 20
b 10
b 20
b 10
结果
a 50
b 30
正常情况计数是一直累加，但会出现从新累加的情况 求总共的累加值
------解决思路----------------------
你这个既然结果和排序有关。那么你提供个排序啊。
--测试表
mytype counts n
---------- ----------- --------------------
a 10 1
a 20 2
a 30 3
a 10 4
a 20 5
b 10 1
b 20 2
b 10 3
with cte as
(select *,ROW_NUMBER()over(partition by mytype order by (select 2)) as n from mytest),
cte1 as
(select *,1 as level from cte where n=1
union all
select a.*,case when b.counts-a.counts<0 then b.level
else b.level+1 end as level from cte as a join cte1 as b on a.n=b.n +1 and a.mytype=b.mytype)
select mytype, sum(counts) as sums from (select mytype,MAX(counts)as counts from cte1
group by mytype,level) as t
group by mytype
------解决思路----------------------
可以试试这个：
create table 表 (型号 varchar(10), 计数 int)
insert into 表
select 'a', 10 union all
select 'a', 20 union all
select 'a', 30 union all
select 'a', 10 union all
select 'a', 20 union all
select 'b', 10 union all
select 'b', 20 union all
select 'b', 10
go
select 型号,sum(计数) as 计数
from
(
select 型号,MAX(计数) 计数
from
(
select *,
ROW_NUMBER() over(partition by 型号 order by getdate()) rownum,
dense_RANK() over(partition by 型号 order by 计数) ranknum
from 表
)t
group by 型号,rownum - ranknum
)t
group by 型号
/*
型号 计数
a 50
b 30
*/
------解决思路----------------------
with cte as (
select *,ROW_NUMBER() over(order by 型号) as id from 表
)
select 型号,SUM(计数) as 计数 from (
select aa.* from cte as aa left join cte as bb on aa.id+1=bb.id and aa.计数<bb.计数
where bb.计数 is null) as cc
group by cc.型号
------解决思路----------------------
2008环境
CREATE TABLE [dbo].[mytest](
[mytype] [varchar](20) NULL,
[counts] [int] NULL
) ON [PRIMARY]
with temp as (
select ROW_NUMBER()over(order by mytype)id,* from mytest
),
maxrows as(
select isnull((aa.counts-bb.counts),0)maxrow,aa.mytype,aa.counts from temp as aa left join temp as bb on aa.id+1=bb.id
and aa.mytype=bb.mytype where isnull((aa.counts-bb.counts),0)>=0
)
select mytype,SUM(counts) from maxrows group by mytype