求个 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
完整的，添加了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
上面一个条件忘记判断了，执行计划有两个半连接。