日期:2014-05-18 浏览次数:20461 次
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 主表(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 行) */