求一复杂sql,该如何处理

求一复杂sql
数据库中有如下两张表：
主表
bugidbugname
001客户信息不能保存
002产品信息保存时报错

子表
bugidstepidstepnameplandate overdate
001 1问题确认 2007-10-82007-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如何写：
bugidbugnamenowstep
001客户信息不能保存 1/4
002产品信息保存时报错 0/0

谢谢！



[解决办法]

SQL code

select a.* ,nowstep = cast(isnull(t1.wcs,'0') as varchar) + '/' + cast(isnull(t2.zs,'0') as varchar) from 主表 aleft join(select bugid , wcs = count(*) from 子表 where overdate is not null group by bugid) t1 on a.bugid = t1.bugidleft join(select bugid , zs = count(*) from 子表 group by bugid) t2 on a.bugid = t2.bugid
[解决办法]
SQL code
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)goselect    m.*,rtrim(isnull(s.finish,0))+'/'+rtrim(isnull(s.total,0)) as nowstepfrom    Main mleft 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) son    m.bugid=s.bugidgo/*bugid      bugname              nowstep---------- -------------------- ----------------001        客户信息不能保存      1/4002        产品信息保存时报错    0/0*/drop table Main,Subgo
[解决办法]
SQL code
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
[解决办法]
SQL code
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) goselect a.bugid ,a.bugname ,nowstep = cast(isnull(t1.wcs,'0') as varchar) + '/' + cast(isnull(t2.zs,'0') as varchar) from 主表 aleft join(select bugid , wcs = count(*) from 子表 where overdate is not null group by bugid) t1 on a.bugid = t1.bugidleft join(select bugid , zs = count(*) from 子表 group by bugid) t2 on a.bugid = t2.bugiddrop table 主表,子表/*bugid      bugname              nowstep                                                       ---------- -------------------- ------------------------- 001        客户信息不能保存         1/4002        产品信息保存时报错       0/0（所影响的行数为 2 行）*/