求一统计存储过程

[山野市民]

数据：
序号 字段1 状态
1 A OK
2 B OK
3 B No
4 A No
5 A OK
结果：
字段1 状态为OK条数 状态为No条数 总条数
A 2 1 3
B 1 1 2

[csuxp2008]

select 字段1,
sum(case when 状态='OK' then 1 else 0 end) as '状态为OK条数',
sum(case when 状态='No' then 1 else 0 end) as '状态为No条数',
count(*) as '总条数'
from table_name
group by 字段1

[Mr_Nice]

[Quote=引用 1 楼 csuxp2008 的回复:]

SQL code
select 字段1,
sum(case when 状态='OK' then 1 else 0 end) as '状态为OK条数',
sum(case when 状态='No' then 1 else 0 end) as '状态为No条数',
count(*) as '总条数'
from table_name
group by 字段1
[/Quote]

up

[luo7269315]

select 字段1,
sum(case when 状态='OK' then 1 else 0 end),
sum(case when 状态='NOthen 1 else 0 end),
count(*)
from student
group by 字段1

[lb6201883]

select 字段,OK=sum(case 状态 when 'OK' then 1 else 0 end),[ON]=sum(case 状态 when 'No' then 1 else 0 end),总条数=count(*) from t1 group by 字段

[lb6201883]

create table t1
( 序号 int , 字段 nvarchar(10), 状态 nvarchar(10))



insert t1
select 1, 'A', 'OK' union all
select 2 , 'B', 'OK' union all
select 3 , 'B', 'No' union all
select 4 , 'A', 'No' union all
select 5 , 'A', 'OK'


select 字段,OK=sum(case 状态 when 'OK' then 1 else 0 end),[ON]=sum(case 状态 when 'No' then 1 else 0 end),总条数=count(*) from t1 group by 字段