在动易系统中的一个查询SQL语句

在动易系统中的一个查询

 

Select Distinct a.ClassName as  大类名称 ,
       ISNULL ( b.一个月内的更新数量 , 0 ) as 一个月内的更新数量,
       ISNULL ( c.一周内的更新数量 , 0 ) as 一周内的更新数量 ,
       ISNULL ( d.一天之内更新数量 , 0 ) as 一天之内更新数量 from PE_Class a

Left Outer Join (

select f30.ClassName as 大类名称 ,
        count(*) as 一个月内的更新数量 from PE_Soft d30 , PE_Class e30 , PE_Class f30

where DateDiff( Day , d30.UpdateTime , GetDate()) <= 30 and

d30.ClassID = e30.ClassID and e30.ParentID = f30.ClassID  and   d30.ClassID in (

   Select a30.ClassID  from PE_Class a30 , PE_Class c30 where a30.ParentID in (

      select b30.ClassID from PE_Class b30 Where b30.ParentID = 0 and b30.ChannelID = 1014

   ) and a30.ParentID = c30.ClassID

) Group by f30.ClassName

 ) b

ON a.ClassName = b.大类名称

Left Outer Join (

select f07.ClassName as 大类名称 ,
        count(*) as 一周内的更新数量 from PE_Soft d07 , PE_Class e07 , PE_Class f07

where DateDiff( Day , d07.UpdateTime , GetDate()) <= 7 and

d07.ClassID = e07.ClassID and e07.ParentID = f07.ClassID  and   d07.ClassID in (

   Select a07.ClassID  from PE_Class a07 , PE_Class c07 where a07.ParentID in (

      select b07.ClassID from PE_Class b07 Where b07.ParentID = 0 and b07.ChannelID = 1014

   ) and a07.ParentID = c07.ClassID

) Group by f07.ClassName

 ) c

ON a.ClassName = c.大类名称

Left Outer Join (

select f01.ClassName as 大类名称 ,
        count(*) as 一天之内更新数量 from PE_Soft d01 , PE_Class e01 , PE_Class f01

where DateDiff( Day , d01.UpdateTime , GetDate()) <= 1 and

d01.ClassID = e01.ClassID and e01.ParentID = f01.ClassID  and   d01.ClassID in (

   Select a01.ClassID  from PE_Class a01 , PE_Class c01 where a01.ParentID in (

      select b01.ClassID from PE_Class b01 Where b01.ParentID = 0 and b01.ChannelID = 1014

   ) and a01.ParentID = c01.ClassID

) Group by f01.ClassName

 ) d

ON a.ClassName = d.大类名称

Where a.ParentID = 0 and a.ChannelID = 1014

Order by a.ClassName