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
