递归查询和子查询连接

 WITH T--根节点  新房媒体集团
                             AS (SELECT *
                                 FROM   SlUrlKpiBiTree WITH(NOLOCK)
                                 WHERE  id = '5'
                                ),
                             T1--子节点 各个城市
                             AS (SELECT *,
                                        0 AS childLevel
                                 FROM   T
                                 UNION ALL
                                 SELECT SlUrlKpiBiTree.*,
                                        1 AS childLevel
                                 FROM   SlUrlKpiBiTree WITH(NOLOCK)
                                        JOIN T1
                                          ON SlUrlKpiBiTree.pid = T1.id
                                ),
                             T2--叶子节点 各个销售和组名
                             AS (SELECT *
                                 FROM   SlUrlKpiBiTree a WITH(NOLOCK)
                                 WHERE  NOT EXISTS(SELECT pid
                                                   FROM   SlUrlKpiBiTree
                                                   WHERE  pid = a.ID)--id not in (select pid from SlUrlKpiBiTree)
                                ),
                             T3--除去组名保留销售
                             AS (SELECT *
                                 FROM   T1 a
                                 WHERE  [type] = -1
                                        AND EXISTS(SELECT id
                                                   FROM   T2
                                                   WHERE  id = a.id)--id in (select id from T2)
                                ),
                             T4--取销售邮箱
                             AS (SELECT UrlOwner
                                 FROM   T3
                                        JOIN SlUrlKpiTreeToUrlOwner WITH(NOLOCK)
                                          ON T3.ID = SlUrlKpiTreeToUrlOwner.TreeID 
                                ),
                             T5--取各个销售每天的pc流量
                             AS (SELECT *
                                 FROM   SlUVForUrlOwnerForChannel WITH(NOLOCK)
                                 WHERE  UrlOwner IN (SELECT UrlOwner
                                                     FROM   T4)
                                        AND Channel <> '*'
                                        AND datadate = @DataDate
                                        AND groupdefineid IN (SELECT id
                                                              FROM   SlUrlKpiChannelDefine
                                                              WHERE  GroupDefineID = 2
                                                                     AND id IN ( 1, 3, 5, 7 )--pc
                                                             )),
                             T6--取各个销售每天的wap流量
                             AS (SELECT *
                                 FROM   SlUVForUrlOwnerForChannel WITH(NOLOCK)
                                 WHERE  urlowner IN (SELECT UrlOwner
                                                     FROM   T4)
                                        AND Channel <> '*'
                                        AND datadate = @DataDate
                                        AND groupdefineid IN (SELECT id
                                                              FROM   SlUrlKpiChannelDefine
                                                              WHERE  GroupDefineID = 2
                                                                     AND id IN ( 2, 4, 6, 8 )--wap
                                                             )),
                             T7
                             AS --取PC频道名称
                             (SELECT T5.*,
                                     SlUrlKpiChannelDefine.ChannelName,
                                     SlUrlKpiChannelDefine.CodeExpression
                              FROM   T5
                                     JOIN SlUrlKpiChannelDefine
                                       ON T5.groupdefineid = SlUrlKpiChannelDefine.groupdefineid),
                             T8
                             AS --取WAP频道名称
                             (SELECT T6.*,
                                     SlUrlKpiChannelDefine.ChannelName,
                                     SlUrlKpiChannelDefine.CodeExpression
                              FROM   T6
                                     JOIN SlUrlKpiChannelDefine
                                       ON T6.groupdefineid = SlUrlKpiChannelDefine.groupdefineid),
                             T9
                             AS --pc流量统计
                             (SELECT urlowner,
                                     channelname,
                                     DataDate,
                                     Sum(Countquantity)   AS PCCountquantity,
                                     Sum(VisitorQuantity) AS PCVisitorQuantity
                              FROM   T7
                              GROUP  BY urlowner,
                                        channelname,
                                        DataDate),
                             T10
                             AS --wap流量统计
                             (SELECT urlowner,
                                     channelname,
                                     DataDate,
                                     Sum(Countquantity)   AS WapCountquantity,
                                     Sum(VisitorQuantity) AS WapVisitorQuantity
                              FROM   T8
                              GROUP  BY urlowner,
                                        channelname,
                                        DataDate),
                             T11
                             AS (SELECT T9.UrlOwner,
                                        T9.ChannelName,
                                        T9.DataDate,
                                        T9.PCCountquantity,
                                        T9.PCVisitorQuantity,
                                        T10.WapCountquantity,
                                        T10.WapVisitorQuantity
                                 FROM   T9--根据所属人、频道、日期，连接PC WAP两个子查询
                                        JOIN T10
                                          ON T9.ChannelName = T10.ChannelName
                                             AND T9.DataDate = T10.dataDate
                                             AND T9.urlowner = T10.urlowner)
                        SELECT T1.Name AS CityName,
                               T11.UrlOwner,
                               T11.ChannelName,
                               T11.DataDate,
                               T11.PCCountquantity,
                               T11.PCVisitorQuantity,
                               T11.WapCountquantity,
                               T11.WapVisitorQuantity
                        FROM   T11
                               JOIN SlUrlKpiTreeToUrlOwner
                                 ON T11.UrlOwner = SlUrlKpiTreeToUrlOwner.UrlOwner
                               JOIN T1
                                 ON T1.ID = SlUrlKpiTreeToUrlOwner.TreeID
                                    WHERE  T1.type=3