一个Sql备注-白红宇

一个Sql备注

阅读量：6697 次

发布时间：2019-06-25

本文共 2375 字，大约阅读时间需要 7 分钟。

;WITH CTE AS (SELECT [RGTCID]      ,ltrim(rtrim(T.N.value('.','varchar(100)'))) as  Querystr  FROM [R_GT_Collection]  cross apply RGTCData.nodes('//*[@data-type="Collection"]/@Query') T(N)  where N.value('../@data-from','varchar(100)') is null),CollectionIDs AS(select y.t_str,count(distinct x.RGTCID) CollectionCount from cte x  CROSS APPLY dbo.f_split(SUBSTRING(Querystr,CHARINDEX('(',Querystr)+1,CHARINDEX(')',Querystr)-CHARINDEX('(',Querystr)-1),',') y  group by y.t_str),CTE2 AS (SELECT [RGTBID]      ,ltrim(rtrim(T.N.value('.','varchar(100)'))) as  Querystr  FROM [R_GT_Brand]  cross apply RGTBData.nodes('//*[@data-type="Collection"]/@Query') T(N)  where N.value('../@data-from','varchar(100)') is null),CollectionIDs2 AS(select y.t_str,count(distinct x.RGTBID) CollectionCount from cte2 x  CROSS APPLY dbo.f_split(SUBSTRING(Querystr,CHARINDEX('(',Querystr)+1,CHARINDEX(')',Querystr)-CHARINDEX('(',Querystr)-1),',') y  group by y.t_str),CTE3 AS (SELECT [RGTGID]      ,ltrim(rtrim(T.N.value('.','varchar(100)'))) as  Querystr  FROM [R_GT_Guru]  cross apply RGTSData.nodes('//*[@data-type="Collection"]/@Query') T(N)  where N.value('../@data-from','varchar(100)') is null),CollectionIDs3 AS(select y.t_str,count(distinct x.RGTGID) CollectionCount from cte3 x  CROSS APPLY dbo.f_split(SUBSTRING(Querystr,CHARINDEX('(',Querystr)+1,CHARINDEX(')',Querystr)-CHARINDEX('(',Querystr)-1),',') y  group by y.t_str),CTE4 AS (SELECT [RGTSID]      ,ltrim(rtrim(T.N.value('.','varchar(100)'))) as  Querystr  FROM [R_GT_Story]  cross apply RGTSData.nodes('//*[@data-type="Collection"]/@Query') T(N)  where N.value('../@data-from','varchar(100)') is null),CollectionIDs4 AS(select y.t_str,count(distinct x.RGTSID) CollectionCount from cte4 x  CROSS APPLY dbo.f_split(SUBSTRING(Querystr,CHARINDEX('(',Querystr)+1,CHARINDEX(')',Querystr)-CHARINDEX('(',Querystr)-1),',') y  group by y.t_str)select c.CollectionID,isnull(m.CollectionCountSum,0) as CollectionCountSum FROM [R_GT_Collection] cleft join (select t_str,sum(CollectionCount) as CollectionCountSum from (select t_str,CollectionCount from CollectionIDsunion allselect t_str,CollectionCount from CollectionIDs2union allselect t_str,CollectionCount from CollectionIDs3union allselect t_str,CollectionCount from CollectionIDs4) z group by t_str) mon c.CollectionID=m.t_strorder by 1

转载于:https://www.cnblogs.com/shikyoh/p/3602169.html

你可能感兴趣的文章

U(优)盘安装FreeBSD-9.0+GNOME_lite桌面

查看>>

模拟产品展示 Flash无法展示的追踪过程

查看>>

Understand Lambda Expressions in 3 minutes（翻译）

查看>>

dom4j的读写xml文件，读写xml字符串

查看>>

Node.js 使用jQuery取得Nodejs http服务端返回的JSON对象示例

查看>>