求个 MSSQL 统计方法。
id state times
------------------------------
a 1 0
a 0 13
a 1 0
a 0 46
b 0 566
b 1 0
b 0 15
c 1 0
c 0 480
d 0 960
d 1 0
d 0 90
------------------------------
想要得到的结果
--------------------------------
a 59
b 15
c 480
d 90
--------------------------------
就是 group by id 之后 每个 id 第一条 state 不等于 0 的 sum(times)
数据量在 35W + MS?SQL 统计
[解决办法]
if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (id nvarchar(2),state int,times int)
insert into [TB]
select 'a',1,0 union all
select 'a',0,13 union all
select 'a',1,0 union all
select 'a',0,46 union all
select 'b',0,566 union all
select 'b',1,0 union all
select 'b',0,15 union all
select 'c',1,0 union all
select 'c',0,480 union all
select 'd',0,960 union all
select 'd',1,0 union all
select 'd',0,90
select * from [TB]
SELECT id ,
SUM(CASE WHEN STATE = 0
AND no <> 1 THEN times
ELSE 0
END) AS [sum]
FROM ( SELECT * ,
ROW_NUMBER() OVER ( PARTITION BY id ORDER BY GETDATE() ) AS no
FROM tb
) T
GROUP BY T.id
/*
idsum
a59
b15
c480
d90*/
Create table #tb(id char(1), state int,times int);
Insert Into #tb
Select 'a' ,1,0 Union All
Select 'a' ,0,13 Union All
Select 'a' ,1,0 Union All
Select 'a' , 0,46 Union All
Select 'b',0,566 Union All
Select 'b',1,0 Union All
Select 'b',0,15 Union All
Select 'c',1,0 Union All
Select 'c',0,480 Union All
Select 'd',0,960 Union All
Select 'd',1,0 Union All
Select 'd' , 0 , 90
---------------------
Go
With t as
(
Select *,ROW_NUMBER() Over(PARTITION by id order by id) as rn
From #tb
)
Select id,SUM(times)
From t
Where rn >= 1 And(rn > 1 Or state > 0)
Group by id
-----------------------------
a59
b15
c480
d90
drop table #tb;
Create table #tb(id char(1), state int,times int, gid int identity(1,1) );
Insert Into #tb (id,state,times)
Select 'a' ,1,0 Union All
Select 'a' ,0,13 Union All
Select 'a' ,1,0 Union All
Select 'a' , 0,46 Union All
Select 'b',0,566 Union All
Select 'b',1,0 Union All
Select 'b',0,15 Union All
Select 'c',1,0 Union All
Select 'c',0,480 Union All
Select 'd',0,960 Union All
Select 'd',1,0 Union All
Select 'd' , 0 , 90
---------------------
Go
Select id,SUM(Times) as total
From
#tb
Where gid Not In
(Select gId
From #tb as t1
WHere Not exists(
Select *
From #tb as t2
Where t2.id = t1.id
And t2.gid < t1.gid
))
Group by id
drop table #tb;
Create table #tb(id char(1), state int,times int, gid int identity(1,1) );
Insert Into #tb (id,state,times)
Select 'a' ,1,0 Union All
Select 'a' ,0,13 Union All
Select 'a' ,1,0 Union All
Select 'a' , 0,46 Union All
Select 'b',0,566 Union All
Select 'b',1,0 Union All
Select 'b',0,15 Union All
Select 'c',1,0 Union All
Select 'c',0,480 Union All
Select 'd',0,960 Union All
Select 'd',1,0 Union All
Select 'd' , 0 , 90
---------------------
Go
Select id,SUM(Times) as total
From
#tb
Where gid Not In
(Select gId
From #tb as t1
WHere Not exists(
Select *
From #tb as t2
Where t2.id = t1.id
And t2.gid < t1.gid )
And t1.state = 0--上面忘记了
)
Group by id
-----------
a 59
b 15
c 480
d 90