求一查询语句，请高手帮忙！

[fengyinglois]

假如我有个表，字段如下：
工号 状态
80466 WD
80466 WD
80466 YD
80466 YD
80466 YD
80466 SC
80466 BC
80467 WD
我的问题是想用一条查询语句查得80466的状态数：WD=2、YD=3、SC=1、BC=1

[victorycyz]

select 状态, count(*) as 状态数
from tablename
where 工号='80466'
group by 状态

[leeboyan]

select count(状态) from yourtable group by 状态,工号 having 工号='80466'

[ghosthjt]

select 状态, count(*) as 状态数 from tablename where 工号='80466'
group by 状态

[jastion]

create table tab (工号 varchar(10) ,状态 varchar(10))

insert into tab select '80466' ,'WD'
union select '80466' ,'YD'
union select '80466' ,'YD'
union select '80466' ,'YD'
union select '80466' ,'SC'
union select '80466' ,'BC'
union select '80467' ,'WD'


select 工号 ,
sum(case 状态 when 'WD' then num else 0 end ) as WD
, sum(case 状态 when 'YD' then num else 0 end ) as YD
, sum(case 状态 when 'SC' then num else 0 end ) as SC
, sum(case 状态 when 'BC' then num else 0 end ) as BC

from (
select 工号 , 状态 ,count(工号) as num from tab
group by 工号 , 状态
) A
group by 工号
/*
工号 WD YD SC BC
---------- ----------- ----------- ----------- -----------
80466 1 1 1 1
80467 1 0 0 0

*/
drop table tab