表A:
NAME STATUS
A 正常
A 正常
A 违章
A 违章
A 违规
A 违规
A 违规
A 违章
A 违规
A 违规求一条SQL查询语句得到如下查询结果:
NAME STATUS count
A 正常 2
A 违章 2
A 违规 3
A 违章 1
A 违规 2
NAME STATUS
A 正常
A 正常
A 违章
A 违章
A 违规
A 违规
A 违规
A 违章
A 违规
A 违规求一条SQL查询语句得到如下查询结果:
NAME STATUS count
A 正常 2
A 违章 2
A 违规 3
A 违章 1
A 违规 2
create table A(NAME varchar(10), STATUS varchar(10))
insert A select 'A', '正常'
union all select 'A', '正常'union all select 'A', '违章'
union all select 'A', '违章'union all select 'A', '违规'
union all select 'A', '违规'
union all select 'A', '违规'union all select 'A', '违章'union all select 'A', '违规'
union all select 'A', '违规'select ID=identity(int, 1, 1),* into #T from Aselect NAME, STATUS, count=count(*) from
(
select A.NAME, A.STATUS, A.id, groupID=(count(*)-sum(case when A.STATUS=B.STATUS then 1 else 0 end))
from #T A, #T B
where A.id>=B.id
group by A.NAME, A.STATUS, A.id
)tmp
group by NAME, STATUS, groupID
order by min(id)--result
NAME STATUS count
---------- ---------- -----------
A 正常 2
A 违章 2
A 违规 3
A 违章 1
A 违规 2(5 row(s) affected)