如何将CTE查询与SQL Server 2008中的另一个表联接 [英] How to join CTE query with another table in SQL Server 2008
问题描述
我创建了一个CTE查询，并且当CTE中的数据与另一个表匹配时，我设法加入了CTE。
I have created a CTE query, and I manage to join the CTE when the data in CTE is match with another table.
例如，这就是我的CTE查询结果的样子：
For example, this is how my CTE query result looks like:
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
这是我设法将其加入的表：
And this is the table I manage to join it to:
ID NAME CUS
---------------------
1 TEST1 C0001
2 TEST2 C0002
3 TEST3 C0003
4 TEST4 C0004
这是我选择CTE查询的代码
And this is my code to select CTE 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
)
从ArCustomer中选择ArCustomer.Name，Query.Cus
左联接ArCustomer.Customer = Query.ArCustomer上的查询
.Customer
的订单由ArCustomer.Name
select ArCustomer.Name, Query.Cus from ArCustomer left join Query on ArCustomer.Customer = Query.ArCustomer.Customer order by ArCustomer.Name
推荐答案
[1]使用两个CTE（BaseQuery，查询和表之间的联接）和查询）：
[ 1 ] Using two CTEs (BaseQuery, Query and join between table and 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运算符：
[ 2 ] Second solution is based also on two CTEs (BaseQuery and Query) but instead of JOIN is using APPLY operator thus:
;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）中，然后将JOIN temp（#Results）表与另一个表（Table1）一起插入：
[ 3 ] Another solution is to insert those rows (extracted from Comment column) into a temp table (#Results) and then JOIN temp (#Results) table with another table (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
这篇关于如何将CTE查询与SQL Server 2008中的另一个表联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
