如何将CTE查询与SQL Server 2008中的另一个表连接起来

Question

我创建了一个CTE查询，并且当CTE中的数据与另一个表匹配时，我设法加入了CTE。

例如，这是我的CTE查询结果的样子：

ID  NAME REG  INV  CUS  BR 
----------------------------------------------- 
1  A0001 R0001 I0001 C0001  B0001 
2  A0002 R0002 I0002 C0002  B0002 
3  A0003 R0003 I0003 C0003  B0003 
4  A0004 R0004 I0004 C0004  B0004 

这是我设法把它加入表：

ID NAME  CUS 
--------------------- 
1  TEST1 C0001 
2  TEST2 C0002 
3  TEST3 C0003 
4  TEST4 C0004 

这是我的代码选择CTE查询

;WITH BaseQuery AS 
(
    SELECT 
     Id, Name, Comment, 
     CONVERT(XML, '<root><item>' 
       + REPLACE(
        REPLACE(
         REPLACE(
          REPLACE((SELECT Comment AS '*' FOR XML PATH('')), 
          'Reg:', 
          '</item><item type="Reg">'), 
         'Inv:', 
         '</item><item type="Inv">'), 
        'Cus:', 
        '</item><item type="Cus">'), 
       'Br:', 
       '</item><item type="Br">') + '</item></root>') CommentAsXml 
    FROM  
     GenTransaction 
), Query (
SELECT 
    Id, Name, Comment, 
    Reg = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Reg"])[1]', 'VARCHAR(11)'))), 
    Inv = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Inv"])[1]', 'VARCHAR(11)'))), 
    Cus = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Cus"])[1]', 'VARCHAR(11)'))), 
    Br = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Br"])[1]', 'VARCHAR(11)'))) 
FROM  
    BaseQuery bq 
) 

选择ArCustomer.Name，Query.Cus 从ArCustomer左加入Q上ArCustomer.Customer = Query.ArCustomer.Customer 顺序uery 通过ArCustomer.Name

Answer 1

[1]使用两个CTE（BaseQuery，Query和表与Query之间的连接）：

;WITH BaseQuery AS 
(
    SELECT 
     Id, Name, Comment, 
     CONVERT(XML, '<root><item>' 
       + REPLACE(
        REPLACE(
         REPLACE(
          REPLACE((SELECT Comment AS '*' FOR XML PATH('')), 
          'Reg:', 
          '</item><item type="Reg">'), 
         'Inv:', 
         '</item><item type="Inv">'), 
        'Cus:', 
        '</item><item type="Cus">'), 
       'Br:', 
       '</item><item type="Br">') + '</item></root>') CommentAsXml 
    FROM  
     GenTransaction 
), Query (
SELECT 
    Id, Name, Comment, 
    Reg = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Reg"])[1]', 'VARCHAR(11)'))), 
    Inv = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Inv"])[1]', 'VARCHAR(11)'))), 
    Cus = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Cus"])[1]', 'VARCHAR(11)'))), 
    Br = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Br"])[1]', 'VARCHAR(11)'))) 
FROM  
    BaseQuery bq 
) 
SELECT ... 
FROM Table1 t1 INNER/LEFT OUTER/... JOIN Query q ON ... join condition ... -- Query represents the second CTE 
ORDER BY ... 

[2]第二溶液还基于两个的CTE（BaseQuery和查询），但代替JOIN使用由此APPLY操作：

;WITH BaseQuery AS 
(
    SELECT 
     Id, Name, Comment, 
     CONVERT(XML, '<root><item>' 
       + REPLACE(
        REPLACE(
         REPLACE(
          REPLACE((SELECT Comment AS '*' FOR XML PATH('')), 
          'Reg:', 
          '</item><item type="Reg">'), 
         'Inv:', 
         '</item><item type="Inv">'), 
        'Cus:', 
        '</item><item type="Cus">'), 
       'Br:', 
       '</item><item type="Br">') + '</item></root>') CommentAsXml 
    FROM  
     GenTransaction 
), Query (
SELECT 
    Id, Name, Comment, 
    Reg = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Reg"])[1]', 'VARCHAR(11)'))), 
    Inv = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Inv"])[1]', 'VARCHAR(11)'))), 
    Cus = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Cus"])[1]', 'VARCHAR(11)'))), 
    Br = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Br"])[1]', 'VARCHAR(11)'))) 
FROM  
    BaseQuery bq 
) 
SELECT ... t1.Col1 ... x.Col2 ... 
FROM Table1 t1 
OUTER/CROSS APPLY (
    SELECT ... 
    FROM Query q 
    WHERE ... join condition ... -- Query represents the second CTE 
) x 
ORDER BY ... 

[3]另一种解决方案是插入这些行（从柱的评论中提取）到一个临时表（#Results）然后再加入温度（#Results）表与另一个表（表1）：

;WITH BaseQuery AS 
(
    SELECT 
     Id, Name, Comment, 
     CONVERT(XML, '<root><item>' 
       + REPLACE(
        REPLACE(
         REPLACE(
          REPLACE((SELECT Comment AS '*' FOR XML PATH('')), 
          'Reg:', 
          '</item><item type="Reg">'), 
         'Inv:', 
         '</item><item type="Inv">'), 
        'Cus:', 
        '</item><item type="Cus">'), 
       'Br:', 
       '</item><item type="Br">') + '</item></root>') CommentAsXml 
    FROM  
     GenTransaction 
) 
SELECT 
    Id, Name, Comment, 
    Reg = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Reg"])[1]', 'VARCHAR(11)'))), 
    Inv = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Inv"])[1]', 'VARCHAR(11)'))), 
    Cus = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Cus"])[1]', 'VARCHAR(11)'))), 
    Br = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Br"])[1]', 'VARCHAR(11)'))) 
INTO #Results 
FROM  
    BaseQuery bq; -- ORDER BY here should be used within final query 

SELECT ... 
FROM Table1 t1 INNER/LEFT OUTER/... JOIN #Results r ON ... join condition build using t1./r... ... 
ORDER BY ... 


Id, Name, Comment 

Answer 2

从WITH common_table_expression (Transact-SQL)

文档指定临时命名的结果集，被称为公用表 表达式（CTE）。这是从一个简单的查询中派生出来的，并在单个SELECT，INSERT，UPDATE或 DELETE语句的执行范围内定义了 。

由于CTE结果范围的只有一个声明，你需要在表变量保存结果SELECT查询或创建另一个CTE

随着表变量

DECLARE @BaseData AS TABLE (
    Id INT, 
    Name VARCHAR(50), 
    Comment VARCHAR(500), 
    Reg VARCHAR(11), 
    Inv VARCHAR(11), 
    Cus VARCHAR(11), 
    Br VARCHAR(11) 
) 

;WITH BaseQuery AS 
(
    -- your CTE 
) 

INSERT INTO @BaseData 
SELECT Id, Name, Comment, 
    Reg = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Reg"])[1]','VARCHAR(11)'))), 
    Inv = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Inv"])[1]','VARCHAR(11)'))), 
    Cus = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Cus"])[1]','VARCHAR(11)'))), 
    Br = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Br"])[1]','VARCHAR(11)'))) 
FROM BaseQuery 
ORDER BY Id, Name, Comment 

-- Join it with another table 
SELECT * 
FROM AnotherTable at 
INNER JOIN @BaseData bd ON bd.Id = at.SomeId 

与另一个CTE

;WITH BaseQuery AS 
(
    -- your CTE 
) 
, 
BaseResult AS 
(
    SELECT Id, Name, Comment, 
    Reg = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Reg"])[1]','VARCHAR(11)'))), 
    Inv = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Inv"])[1]','VARCHAR(11)'))), 
    Cus = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Cus"])[1]','VARCHAR(11)'))), 
    Br = LTRIM(RTRIM(bq.CommentAsXml.value('(root/item[@type="Br"])[1]','VARCHAR(11)'))) 
    FROM BaseQuery 
) 

-- Join second result with another table 
SELECT * 
FROM AnotherTable at 
INNER JOIN BaseResult br ON br.Id = at.SomeId