我有三张桌子
Master {MasterID, Desc},
Detail {MasterID, DetailID, ItemID},
Items {ItemID, ItemDesc})
我要选择主要信息、详细信息(作为主要信息中的SubElement )、项目(在相同的详细信息元素中)
SELECT Master.MasterID, Master.Desc, Detail.DetailID, Detail.ItemID, Items.ItemDesc
FROM Master
LEFT JOIN Detail
LEFT JOIN Items
ON Detail.ItemID = Items.ItemID
ON Master.MasterID = Detail.MasterID
FOR XML AUTO, ELEMENTS
它给出的结果如下:
<Master>
<MasterID>1</MasterID>
<Desc>Master1</Desc>
<Detail>
<DetailID>1</DetailID>
<ItemID>1</ItemID>
<Items><ItemDesc>ItemDesc1</ItemDesc></Items>
</Detail>
</Master>
但我的目标是
<Master>
<MasterID>1</MasterID>
<Desc>Master1</Desc>
<Detail>
<DetailID>1</DetailID>
<ItemID>1</ItemID>
<ItemDesc>ItemDesc1</ItemDesc>
</Detail>
</Master>'
我怎样才能用最佳实践方式做到这一点呢?
发布于 2012-01-10 03:46:00
使详细信息和商品描述从单个查询返回,如下所示:
SELECT
Master.MasterID,
Master.Desc,
Detail.DetailID,
Detail.ItemID,
Detail.ItemDesc
FROM
Master
LEFT JOIN (
select
d.MasterID,
d.DetailID,
d.ItemID,
i.ItemDesc
from
Detail d
left join items i on
d.itemid = i.itemid
) Detail ON
Master.MasterID = Detail.MasterID
FOR XML AUTO, ELEMENTS
发布于 2012-01-10 05:45:43
您可以像这样使用for xml path
。
select m.MasterID,
m.[Desc],
(select d.DetailID,
d.ItemID,
i.ItemDesc
from Detail as d
inner join Items as i
on d.ItemID = i.ItemID
where d.MasterID = m.MasterID
for xml path('Detail'), type)
from Master as m
for xml path('Master')
https://stackoverflow.com/questions/8793813
复制相似问题