一、原始 写法
select a.tdtname,b.num,c.num from
(select tdtname from dbo.IKNOW_STATISTICS_U2000V1R7C00 where type='TDT') a
left join
(
select tdtname,count(*) as num from dbo.IKNOW_STORY_U2000V1R7C00 where tdtname in (
select tdtName from dbo.IKNOW_STATISTICS_U2000V1R7C00 where type='TDT')
group by tdtName
) b on a.tdtname = b.tdtname
left join
(
select tdtname,count(*) as num from dbo.IKNOW_STORY_U2000V1R7C00 where tdtname in (
select tdtName from dbo.IKNOW_STATISTICS_U2000V1R7C00 where type='TDT')
and jirastatename='closed'
group by tdtName
) c on a.tdtname = c.tdtname;
二、效率高一些的写法
select b.tdtname,b.num,c.num from
(
select a.tdtname,count(b1.tdtname) as num from
IKNOW_STATISTICS_U2000V1R7C00 a
left join
IKNOW_STORY_U2000V1R7C00 b1
on a.tdtname = b1.tdtname
where a.type='TDT'
group by a.tdtName
) b
left join
(
select a.tdtname,count(c1.tdtname) as num from
IKNOW_STATISTICS_U2000V1R7C00 a
left join
IKNOW_STORY_U2000V1R7C00 c1
on a.tdtname = c1.tdtname and c1.jirastatename='closed'
where a.type='TDT'
group by a.tdtName
) c on b.tdtname = c.tdtname;
三、最终写法
select a.tdtname,b.num,c.num from
(select tdtname from IKNOW_STATISTICS_U2000V1R7C00 where type='TDT') a
left join
(
select tdtname,count(*) as num from dbo.IKNOW_STORY_U2000V1R7C00 group by tdtName
) b on a.tdtname = b.tdtname
left join
(
select tdtname,count(*) as num from dbo.IKNOW_STORY_U2000V1R7C00 where jirastatename='closed'
group by tdtName
) c on a.tdtname = c.tdtname
order by a.tdtname;