正常情况计数是一直累加 比如
a 10
a 20
a 30
那就直接就统计30了
但是中途会出现从新计数
a 10
a 20
那这次统计就是20
那总共a 计数 50
a 10
a 20
a 30
那就直接就统计30了
但是中途会出现从新计数
a 10
a 20
那这次统计就是20
那总共a 计数 50
解决方案 »
- 忘记备份SQL数据库,如何恢复数据库(那两个MDF、LOG数据库文件都在)
- 如何构建发布服务器和订阅服务器
- 求一个SQL语句,在线等立即给分100!
- 两个数据库程序如何保存一定顺序访问数据库?
- 大家帮我看看SET @cmd = 'EXEC sp_by_id' + ' ' + @bID + ',' + @ret + ' output'
- 求助一个数据库导出的问题!
- 请教如何添加索引?
- 这名SQL语句该怎么写?(在线等)
- 如何能通过SQL语句获得我需要的行显示方式?
- sql server老手看过来:如何更改数据库的逻辑名字。
- 从一个数据库A到另一个数据库B 复制数据的问题
- 批量导入数据,外键该怎么处理?
--测试表
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 3with 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
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
goselect 型号,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
*/
感觉这逻辑有点取巧吧。要是
select 'a' as 型号 , 10 as 计数 union all
select 'a', 20 union all
select 'a', 30 union all
select 'a', 15 union all
select 'a', 20 union all
select 'b', 10 union all
select 'b', 20 union all
select 'b', 10)这样就不行 啊
感觉这逻辑有点取巧吧。要是
select 'a' as 型号 , 10 as 计数 union all
select 'a', 20 union all
select 'a', 30 union all
select 'a', 15 union all
select 'a', 20 union all
select 'b', 10 union all
select 'b', 20 union all
select 'b', 10)这样就不行 啊关键还是的看楼主的逻辑是什么
感觉这逻辑有点取巧吧。要是
select 'a' as 型号 , 10 as 计数 union all
select 'a', 20 union all
select 'a', 30 union all
select 'a', 15 union all
select 'a', 20 union all
select 'b', 10 union all
select 'b', 20 union all
select 'b', 10)这样就不行 啊关键还是的看楼主的逻辑是什么他的逻辑就是对于每一个型号。如果下一行是递增的。只取下一个。直到最后一行或者下一行是递减的(楼主没说如果相等怎么处理)如果存在递减 重新计数。然后把每一个型号求和。
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.型号
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 mytypeselect * from mytestinsert into mytest values('a',10)
insert into mytest values('a',20)
insert into mytest values('a',30)
insert into mytest values('a',10)
insert into mytest values('a',5)
insert into mytest values('a',10)
insert into mytest values('a',15)
insert into mytest values('a',10)
insert into mytest values('a',12)
insert into mytest values('b',10)
insert into mytest values('b',20)
insert into mytest values('b',10)
insert into mytest values('b',20)
insert into mytest values('b',30)
SELECT 1,'a',10 UNION ALL
SELECT 2,'a',20 UNION ALL
SELECT 3,'a',30 UNION ALL
SELECT 4,'a',10 UNION ALL
SELECT 5,'a',20 UNION ALL
SELECT 6,'b',10 UNION ALL
SELECT 7,'b',20 UNION ALL
SELECT 8,'b',10
),
t AS (
SELECT t1.型号,
t1.计数
FROM test t1
LEFT JOIN test t2
ON t2.型号 = t1.型号
AND t2.id = t1.id+1
WHERE t2.计数 < t1.计数
OR t2.计数 IS NULL
)
SELECT 型号,
SUM(计数) 计数
FROM t
GROUP BY 型号
型号 计数
---- -----------
a 50
b 30