求一复杂sql

[l8w8h]

数据库中有如下两张表：
主表
bugid bugname
001 客户信息不能保存
002 产品信息保存时报错

子表
bugid stepid stepname plandate overdate
001 1 问题确认 2007-10-8 2007-10-10
001 2 分配问题 2007-10-13
001 3 问题修改 2007-10-15
001 4 问题关闭 2007-10-22

情况说明：
1、问题001分了四个处理步骤
2、问题001第一步骤已经于2007-10-10完成
3、现在问题001处于第二个步骤
4、问题002尚未拆分步骤

请问要出下面结果的sql如何写：
bugid bugname nowstep
001 客户信息不能保存 1/4
002 产品信息保存时报错 0/0

谢谢！

[fa_ge]

不明白

[dawugui]

select a.* ,nowstep = cast(isnull(t1.wcs,'0') as varchar) + '/' + cast(isnull(t2.zs,'0') as varchar) from 主表 a
left join
(select bugid , wcs = count(*) from 子表 where overdate is not null group by bugid) t1 on a.bugid = t1.bugid
left join
(select bugid , zs = count(*) from 子表 group by bugid) t2 on a.bugid = t2.bugid

[子陌红尘]

create table Main(bugid varchar(10),bugname varchar(20))
insert into Main values('001','客户信息不能保存')
insert into Main values('002','产品信息保存时报错') 

create table Sub(bugid varchar(10),stepid int,stepname varchar(10),plandate datetime,overdate datetime)
insert into Sub values('001',1,'问题确认','2007-10-08','2007-10-10')
insert into Sub values('001',2,'分配问题','2007-10-13',null)
insert into Sub values('001',3,'问题修改','2007-10-15',null)
insert into Sub values('001',4,'问题关闭','2007-10-22',null)
go


select
    m.*,rtrim(isnull(s.finish,0))+'/'+rtrim(isnull(s.total,0)) as nowstep
from
    Main m
left join
    (select bugid,count(1) as total,sum(case when overdate is null then 0 else 1 end) as finish from sub group by bugid) s
on
    m.bugid=s.bugid
go

/*
bugid      bugname              nowstep
---------- -------------------- ----------------
001        客户信息不能保存      1/4
002        产品信息保存时报错    0/0
*/

drop table Main,Sub
go

[-狙击手-]

select bugid,bugname 
,ltrim((select count(*) from table where bugid = a.bugid and overdate is null))+'/'+
ltrim((select count(*) from table where bugid = a.bugid ))
from table a

[-狙击手-]

create table Main(bugid varchar(10),bugname varchar(20))
insert into Main values('001','客户信息不能保存')
insert into Main values('002','产品信息保存时报错') 

create table Sub(bugid varchar(10),stepid int,stepname varchar(10),plandate datetime,overdate datetime)
insert into Sub values('001',1,'问题确认','2007-10-08','2007-10-10')
insert into Sub values('001',2,'分配问题','2007-10-13',null)
insert into Sub values('001',3,'问题修改','2007-10-15',null)
insert into Sub values('001',4,'问题关闭','2007-10-22',null)
go









select bugid,bugname 
,ltrim((select count(*) from Sub where bugid = a.bugid and overdate is not null))+'/'+
ltrim((select count(*) from Sub where bugid = a.bugid ))
from Main a


/*

bugid      bugname                                        
---------- -------------------- ------------------------- 
001        客户信息不能保存             1/4
002        产品信息保存时报错            0/0

*/
drop table main,sub

[dawugui]

create table 主表(bugid varchar(10),bugname varchar(20))
insert into 主表 values('001', '客户信息不能保存') 
insert into 主表 values('002', '产品信息保存时报错' ) 
create table 子表 (bugid int,stepid int,stepname varchar(10),plandate datetime,overdate datetime) 
insert into 子表 values('001',       1,         '问题确认',                   '2007-10-8' ,'2007-10-10') 
insert into 子表 values('001',       2,         '分配问题',                   '2007-10-13', null)
insert into 子表 values('001',       3,         '问题修改',                   '2007-10-15', null) 
insert into 子表 values('001',       4,         '问题关闭',                   '2007-10-22', null) 
go

select a.bugid ,a.bugname ,nowstep = cast(isnull(t1.wcs,'0') as varchar) + '/' + cast(isnull(t2.zs,'0') as varchar) from 主表 a
left join
(select bugid , wcs = count(*) from 子表 where overdate is not null group by bugid) t1 on a.bugid = t1.bugid
left join
(select bugid , zs = count(*) from 子表 group by bugid) t2 on a.bugid = t2.bugid

drop table 主表,子表

/*
bugid      bugname              nowstep                                                       
---------- -------------------- ------------------------------------------------------------- 
001        客户信息不能保存         1/4
002        产品信息保存时报错       0/0

（所影响的行数为 2 行）
*/