SQL问题5合一.

[pj96127]

select count(*) as 正式 from ht_empy_dept where cpsnno in(select cpsnno from ht_empy where ht_empy.cstatus='正式' and ht_empy.dleave IS NULL and dcome <'2008-01-01') and corg='100001'

select count(*) as 试用 from ht_empy_dept where cpsnno in(select cpsnno from ht_empy where ht_empy.cstatus='试用' and ht_empy.dleave IS NULL and dcome <'2008-01-01') and corg='100001'

select count(*) as 实习 from ht_empy_dept where cpsnno in(select cpsnno from ht_empy where ht_empy.cstatus='实习' and ht_empy.dleave IS NULL and dcome <'2008-01-01') and corg='100001'

select count(*) as 临时 from ht_empy_dept where cpsnno in(select cpsnno from ht_empy where ht_empy.cstatus='临时' and ht_empy.dleave IS NULL and dcome <'2008-01-01') and corg='100001'

select count(*) as 外聘 from ht_empy_dept where cpsnno in(select cpsnno from ht_empy where ht_empy.cstatus='外聘' and ht_empy.dleave IS NULL and dcome <'2008-01-01') and corg='100001'

现在这样查每次查询都只查处一种状态的员工数,
为了提高效率,想采用存储过程实现一次查询出5种状态的员工总数.类似下图:


请教各位大哥,这个存储过程应该怎么写啊??

[bqb]

select   
正式=count(case when ht_empy.cstatus='正式' then 1 else 0 end),
试用=count(case when ht_empy.cstatus='试用' then 1 else 0 end),
.
.
.
.
from   ht_empy_dept   where   cpsnno   in(select   cpsnno   from   ht_empy   
where    ht_empy.dleave   IS   NULL   and   dcome   <'2008-01-01')   and   corg='100001' 

[pj96127]

能不能写完全啊..谢谢啦.我对存储过程不是很熟练.

[子陌红尘]

select
    sum(case when b.cstatus='正式' then 1 else 0 end) as 正式,
    sum(case when b.cstatus='试用' then 1 else 0 end) as 试用,
    sum(case when b.cstatus='实习' then 1 else 0 end) as 实习,
    sum(case when b.cstatus='临时' then 1 else 0 end) as 临时,
    sum(case when b.cstatus='外聘' then 1 else 0 end) as 外聘
from
    ht_empy_dept a,
    ht_empy      b
where
    a.cpsnno=b.cpsnno
    and
    a.corg='100001' 
    and
    b.dleave IS NULL 
    and
    b.dcome<'2008-01-01'

[fcuandy]

直接group by不行?

[bqb]

--一个语句就可以了

select
正式=count(case when ht_empy.cstatus='正式' then 1 else 0 end),
试用=count(case when ht_empy.cstatus='试用' then 1 else 0 end),
实习=count(case when ht_empy.cstatus='实习' then 1 else 0 end),
临时=count(case when ht_empy.cstatus='临时' then 1 else 0 end),
外聘=count(case when ht_empy.cstatus='外聘' then 1 else 0 end),
from ht_empy_dept where cpsnno in(select cpsnno from ht_empy
where ht_empy.dleave IS NULL and dcome <'2008-01-01') and corg='100001'

[dawugui]

使用case when吧

[bqb]

惭愧，我上面的错了，晕！！！
-----------
 
select   
正式=sum(case when ht_empy.cstatus='正式' then 1 else 0 end),
试用=sum(case when ht_empy.cstatus='试用' then 1 else 0 end),
实习=sum(case when ht_empy.cstatus='实习' then 1 else 0 end),
临时=sum(case when ht_empy.cstatus='临时' then 1 else 0 end),
外聘=sum(case when ht_empy.cstatus='外聘' then 1 else 0 end),
from   ht_empy_dept   where   cpsnno   in(select   cpsnno   from   ht_empy   
where    ht_empy.dleave   IS   NULL   and   dcome   <'2008-01-01')   and   corg='100001' 

[pt1314917]

select
    sum(case when b.cstatus='正式' then 1 else 0 end) as 正式,
    sum(case when b.cstatus='试用' then 1 else 0 end) as 试用,
    sum(case when b.cstatus='实习' then 1 else 0 end) as 实习,
    sum(case when b.cstatus='临时' then 1 else 0 end) as 临时,
    sum(case when b.cstatus='外聘' then 1 else 0 end) as 外聘
from ht_empy_dept a,ht_empy b     
where b.dleave IS NULL and b.dcome<'2008-01-01' and a.corg='100001' and a.cpsnno =b.cpsnno 

[dawugui]

上面已经写了这么多了?