记一次SQL xml字段关联查询

需求：

      一张表是APP表，结构如下：

　　

      app_category为该游戏所属的类别ID，xml字段类型

      另一张表是类别表，就ID对应名称，这就不上图了。

      还有一张表是每个游戏的下载记录，结构如下：

　　

     DownLogs_APPId为对应的游戏

     那么需求来了，要查询游戏的下载记录，查询字段中要有游戏的所属类别名称，多个用逗号分隔

     查询结果应如下：

     

最终的执行语句如下：

 

select  APP_Id,APP_Name,LEFT(App_Category,len(App_Category)-1) as App_Category,DownLogs_IMEI,DownLogs_AddTime from (  ----5
select APP_Id,APP_Name,DownLogs_IMEI,DownLogs_AddTime,(   -----4
        select Category_Title+',' from tbl_Category where Category_Id in(  ----2
            select T.C.value('.','nvarchar(5)') as ss from tbl_APP as app cross apply app.APP_Category.nodes('/id') as T(C)  where app_id=tbl_DownLogs.APP_Id) for xml path('')  ----1
        ) as App_Category from (
    select APP_Id,APP_Name,APP_Category,DownLogs_IMEI,DownLogs_AddTime from tbl_APP right join tbl_Download_Logs on tbl_APP.APP_Id= tbl_Download_Logs.DownLogs_APPId   ----3
  ) as tbl_DownLogs
) as T

 

 

 

下面一步步分解

No.1

select T.C.value('.','nvarchar(5)') as ss from tbl_APP as app cross apply app.APP_Category.nodes('/id') as T(C)  where app_id=tbl_DownLogs.APP_Id

 

　　这一句是将xml类型转换为行，示例：

　　注意后面的where条件：app_id=tbl_DownLogs.APP_Id，这个是重点，总感觉怪怪的

No.2

select Category_Title+',' from tbl_Category where Category_Id in
.....

　　这句的功能是将上面的类别ID行使用in来查找对应的title，并使用for xml path('') 来合并为一列，因为是用逗号分隔，所以结尾是会多个一个逗号，这个最后再处理

No.3

select APP_Id,APP_Name,APP_Category,DownLogs_IMEI,DownLogs_AddTime from tbl_APP right join tbl_Download_Logs on tbl_APP.APP_Id= tbl_Download_Logs.DownLogs_APPId

　　普通的连接查询，查出游戏和下载记录：

　　

No.4

　　这就将所需的字段全部查询出来：

　　

No.5

　　

select  APP_Id,APP_Name,LEFT(App_Category,len(App_Category)-1) as App_Category,DownLogs_IMEI,DownLogs_AddTime from (

　　前面说了，类别字段后面会多个逗号，所以这一步就是去掉最后面的逗号

最终大功告成