求一条有点难度SQL查询语句如何写？
表A：
    NAME           STATUS
      A                 正常
      A                 正常
      A                 违章
      A                 违章
      A                 违规
      A                 违规
      A                 违规

求一条SQL查询语句得到如下查询结果：
    NAME           STATUS       count
      A                 正常             2
      A                 违章             2
      A                 违规             3

------解决方案--------------------
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 A

select 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)

------解决方案--------------------
CREATE TABLE A
(
NAME VARCHAR(10),
STATUS VARCHAR(10)
)
INSERT INTO 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 ', '违规 '


CREATE TABLE #t
(
NAME VARCHAR(10),
STATUS VARCHAR(10),
CNT INT
)
/*------------------------------------*/
DECLARE tb CURSOR LOCAL
FOR
SELECT NAME,STATUS FROM A
DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 varchar(10),@I INT
OPEN tb
FETCH tb INTO @col1,@col2
SELECT @col1_old=@col2,@I=0