有一个表三个字段,现在想查询字段st连续相同数据的个数,并统计出现的次数
tid row st
t1 1 a
t1 2 a
t1 3 a
t1 4 a
t1 5 b
t1 6 a
t1 7 a
t1 8 b
t1 9 b
t1 10 b
t1 11 a
t1 12 a
t2 1 b
t2 2 a
t2 3 b
t2 4 c
t2 5 a
t2 6 b
t2 7 b
t2 8 b
t2 9 b
t3 1 b
t3 2 b
t3 3 a
t3 4 b
t3 5 a
t3 6 b
t3 7 c
t4 1 a
....
想得到的结果
t1 a 4 1
t1 a 2 2
t1 a 1 1
t1 b 1 1
t1 b 3 1
t2 a 1 2
t2 b 1 2
t2 b 4 1
t2 c 1 1
t3 a 1 3
t3 b 1 2
t3 b 2 1
t3 c 1 1
t4 a 1 1
....
------解决思路----------------------
CREATE TABLE #tab
(
tid VARCHAR(10),
[row] int,
st VARCHAR(10)
)
insert into #tab values('t1',1,'a')
insert into #tab values('t1',2,'a')
insert into #tab values('t1',3,'a')
insert into #tab values('t1',4,'a')
insert into #tab values('t1',5,'b')
insert into #tab values('t1',6,'a')
insert into #tab values('t1',7,'a')
insert into #tab values('t1',8,'b')
insert into #tab values('t1',9,'b')
insert into #tab values('t1',10,'b')
insert into #tab values('t1',11,'a')
insert into #tab values('t1',12,'a')
insert into #tab values('t2',1,'b')
insert into #tab values('t2',2,'a')
insert into #tab values('t2',3,'b')
insert into #tab values('t2',4,'c')
insert into #tab values('t2',5,'a')
insert into #tab values('t2',6,'b')
insert into #tab values('t2',7,'b')
insert into #tab values('t2',8,'b')
insert into #tab values('t2',9,'b')
insert into #tab values('t3',1,'b')
insert into #tab values('t3',2,'b')
insert into #tab values('t3',3,'a')
insert into #tab values('t3',4,'b')
insert into #tab values('t3',5,'a')
insert into #tab values('t3',6,'b')
insert into #tab values('t3',7,'c')
insert into #tab values('t4',1,'a')
WITH temp
AS ( SELECT * ,number = ROW_NUMBER() OVER ( PARTITION BY tid,st ORDER BY [row] )
FROM #tab t1
)
,temp_2 AS(
SELECT tid ,st,number-row a,COUNT(1) cnt
FROM temp
GROUP BY tid ,st,number-row
)
SELECT tid,st,cnt,COUNT(1) cnt2 FROM temp_2
GROUP BY tid,st,cnt
ORDER BY tid,st