如果沒有“選擇”子查詢，是否可以獲得與此查詢相同的結果？有人對如何解決這個問題有任何建議嗎？
select d.Id as '@DishId', d.Name as '@DishName', (
select p.Id as '@ProdId', p.Name as '@PName', s.Quantity as '@Quantity'
from Structure s
inner join Products p on s.ProdId = p.Id where s.DishId = d.Id
for xml path('Product'), type
) as 'Products'
from Dishes d
for xml path('Dish'), root('Dishes')
此查詢的結果
<Dishes>
<Dish DishId="1" DishName="Potato and Meat">
<Products>
<Product ProdId="1" PName="Meat" Quantity="100" />
<Product ProdId="2" PName="Potato" Quantity="150" />
</Products>
</Dish>
<Dish DishId="2" DishName="Tea">
<Products>
<Product ProdId="3" PName="Water" Quantity="10" />
<Product ProdId="4" PName="Tea" Quantity="150" />
<Product ProdId="5" PName="Sugar" Quantity="10" />
</Products>
</Dish>
</Dishes>
生成資料庫的腳本
https://pastebin.com/V5MkBGQq
uj5u.com熱心網友回復：
帶有子查詢的方法是正確且首選的方法。
這是通過 XQuery 及其 FLWOR 運算式實作所需結果的另一種方法。
這是一個兩步程序。
首先，我們將生成以下原始 XML：
<root>
<row Id="1" Name="Potato and Meat" DishId="1" ProdId="1" Quantity="100" PName="Meat" />
<row Id="1" Name="Potato and Meat" DishId="1" ProdId="2" Quantity="150" PName="Potato" />
<row Id="2" Name="Tea" DishId="2" ProdId="3" Quantity="10" PName="Water" />
<row Id="2" Name="Tea" DishId="2" ProdId="4" Quantity="150" PName="Tea" />
<row Id="2" Name="Tea" DishId="2" ProdId="5" Quantity="10" PName="Sugar" />
</root>
第二步是通過.query()使用 FLWOR 運算式呼叫方法來合成最終的 XML。
SQL
SELECT (SELECT d.* , s.*, p.Name AS PName
FROM dbo.Dishes AS d
INNER JOIN dbo.Structure AS s ON d.Id = s.DishId
INNER JOIN dbo.Products AS p ON s.ProdId = p.Id
FOR XML RAW, TYPE, ROOT('root')
).query('<Dishes>
{
for $d in distinct-values(/root/row/@Id)
return <Dish DishId="{$d}" DishName="{(/root/row[@Id=$d]/@Name)[1]}">
<Products>
{
for $p in /root/row[@Id=$d]/@ProdId
return <Product ProdId="{$p}"
PName="{/root/row[@ProdId=$p]/@PName}"
Quantity="{/root/row[@ProdId=$p]/@Quantity}" />
}
</Products>
</Dish>
}
</Dishes>');
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/434203.html