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

debugcn 发表于 Dev

唐

我创建了一个 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 from ArCustomer left join Query on ArCustomer.Customer = Query.ArCustomer.Customer order by ArCustomer.Name

博格丹·萨利安

[ 1 ] 使用两个 CTE（BaseQuery、Query 和 table 和 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 和 Query），但 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 ] 另一种解决方案是将这些行（从 Comment 列中提取）插入到临时表 (#Results) 中，然后将临时 (#Results) 表与另一个表 (Table1) 连接起来：

;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

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。