表A:
NAME STATUS
A 正常
A 正常
A 违章
A 违章
A 违规
A 违规
A 违规求一条SQL查询语句得到如下查询结果:
NAME STATUS count
A 正常 2
A 违章 2
A 违规 3
NAME STATUS
A 正常
A 正常
A 违章
A 违章
A 违规
A 违规
A 违规求一条SQL查询语句得到如下查询结果:
NAME STATUS count
A 正常 2
A 违章 2
A 违规 3
Name,
STATUS,
Count(*) As [count]
From A
Name,
STATUS,
Count(*) As [count]
From A
Group By Name,STATUS
Name,
STATUS,
Count(*) As [count]
From A
Group By
Name,Status
Create Table A
(NAME Varchar(10),
STATUS Nvarchar(10))
Insert A Select 'A', N'正常'
Union All Select 'A', N'正常'
Union All Select 'A', N'违章'
Union All Select 'A', N'违章'
Union All Select 'A', N'违规'
Union All Select 'A', N'违规'
Union All Select 'A', N'违规'
GO
Select
Name,
STATUS,
Count(*) As [count]
From A
Group By Name,STATUS
GO
Drop Table A
--Result
/*
Name STATUS count
A 正常 2
A 违章 2
A 违规 3
*/
应该是下面这种情况
表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
From #A
group by name,STATUS
用你提供的sql语句查询的出来的结果是
NAME STATUS count
A 正常 2
A 违章 3
A 违规 5不是我要的查询结果,我要的查询结果如下:
NAME STATUS count
A 正常 2
A 违章 2
A 违规 3
A 违章 1
A 违规 2
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, 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
我要求的查询结果如下:
NAME STATUS count
A 正常 2
A 违章 2
A 违规 3
A 违章 1
A 违规 2
而不是下面的查询结果
NAME STATUS count
A 正常 2
A 违章 3
A 违规 5
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)
妙阿~,学习~!
(
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=0WHILE @@FETCH_STATUS=0
BEGIN
IF @col2=@col1_old
SELECT @I=@I+1
ELSE
BEGIN
INSERT #t VALUES(@col1,@col2,@I)
SELECT @col1_old=@col2,@I=1
END
FETCH tb INTO @col1,@col2
END
INSERT #t VALUES(@col1,@col2,@I)
CLOSE tb
DEALLOCATE tb
/*------------------------------------*/
SELECT * FROM #t
DROP TABLE #t
--结果
NAME STATUS CNT
---------- ---------- -----------
A 违章 2
A 违规 2
A 违章 3
A 违规 1
A 违规 2(5 行受影响)
group by status,name