有难度哦,只能帮到这了
Select Row_Number() Over(Order By tid) id,* Into #
From
(
Select 't1' As tid,'1' As row,'a' As st Union All
Select 't1' As tid,'2' As row,'a' As st Union All
Select 't1' As tid,'3' As row,'a' As st Union All
Select 't1' As tid,'4' As row,'a' As st Union All
Select 't1' As tid,'5' As row,'b' As st Union All
Select 't1' As tid,'6' As row,'a' As st Union All
Select 't1' As tid,'7' As row,'a' As st Union All
Select 't1' As tid,'8' As row,'b' As st Union All
Select 't1' As tid,'9' As row,'b' As st Union All
Select 't1' As tid,'1' As row,'b' As st Union All
Select 't1' As tid,'1' As row,'a' As st Union All
Select 't1' As tid,'1' As row,'a' As st Union All
Select 't2' As tid,'1' As row,'b' As st Union All
Select 't2' As tid,'2' As row,'a' As st Union All
Select 't2' As tid,'3' As row,'b' As st Union All
Select 't2' As tid,'4' As row,'c' As st Union All
Select 't2' As tid,'5' As row,'a' As st Union All
Select 't2' As tid,'6' As row,'b' As st Union All
Select 't2' As tid,'7' As row,'b' As st Union All
Select 't2' As tid,'8' As row,'b' As st Union All
Select 't2' As tid,'9' As row,'b' As st Union All
Select 't3' As tid,'1' As row,'b' As st Union All
Select 't3' As tid,'2' As row,'b' As st Union All
Select 't3' As tid,'3' As row,'a' As st Union All
Select 't3' As tid,'4' As row,'b' As st Union All
Select 't3' As tid,'5' As row,'a' As st Union All
Select 't3' As tid,'6' As row,'b' As st Union All
Select 't3' As tid,'7' As row,'c' As st Union All
Select 't4' As tid,'1' As row,'a' As st
) As T
Select *,Cnt=(Select Count(1) From # B Where B.Id>=T.Id And B.Id<(Select Top 1 Id From # A Where A.st<>T.st And A.Id>T.id Order By A.id))
From # T
Select Row_Number() Over(Order By tid) id,* Into #
From
(
Select 't1' As tid,'1' As row,'a' As st Union All
Select 't1' As tid,'2' As row,'a' As st Union All
Select 't1' As tid,'3' As row,'a' As st Union All
Select 't1' As tid,'4' As row,'a' As st Union All
Select 't1' As tid,'5' As row,'b' As st Union All
Select 't1' As tid,'6' As row,'a' As st Union All
Select 't1' As tid,'7' As row,'a' As st Union All
Select 't1' As tid,'8' As row,'b' As st Union All
Select 't1' As tid,'9' As row,'b' As st Union All
Select 't1' As tid,'1' As row,'b' As st Union All
Select 't1' As tid,'1' As row,'a' As st Union All
Select 't1' As tid,'1' As row,'a' As st Union All
Select 't2' As tid,'1' As row,'b' As st Union All
Select 't2' As tid,'2' As row,'a' As st Union All
Select 't2' As tid,'3' As row,'b' As st Union All
Select 't2' As tid,'4' As row,'c' As st Union All
Select 't2' As tid,'5' As row,'a' As st Union All
Select 't2' As tid,'6' As row,'b' As st Union All
Select 't2' As tid,'7' As row,'b' As st Union All
Select 't2' As tid,'8' As row,'b' As st Union All
Select 't2' As tid,'9' As row,'b' As st Union All
Select 't3' As tid,'1' As row,'b' As st Union All
Select 't3' As tid,'2' As row,'b' As st Union All
Select 't3' As tid,'3' As row,'a' As st Union All
Select 't3' As tid,'4' As row,'b' As st Union All
Select 't3' As tid,'5' As row,'a' As st Union All
Select 't3' As tid,'6' As row,'b' As st Union All
Select 't3' As tid,'7' As row,'c' As st Union All
Select 't4' As tid,'1' As row,'a' As st
) As T
Select *,Cnt=(Select Count(1) From # B Where B.Id>=T.Id And B.Id<(Select Top 1 Id From # A Where A.st<>T.st And A.Id>T.id Order By A.id))
From # T
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
t1 a 1 1
这个是没有的。t1里没有a是单独出现过1次的情况