27,580
社区成员
发帖
与我相关
我的任务
分享
问题描述:
执行第二段提示需要将MaterialId也纳入分组,第一段正常
疑问:两段代码差不多,为什么第二段要将MaterialId纳入分组
如果要从表头关联表身来写,怎么才可以不用将MaterialId纳入分组
--创建订单主表
declare @purBillOrderMaster table (BillNo varchar(50))
insert into @purBillOrderMaster
select 'GFP2017040016'
--创建订单明细表
declare @purBillOrderDetail table (BillNo varchar(50),MaterialId varchar(200))
insert into @purBillOrderDetail
select 'GFP2017040016','IBE-14-01-0054-0002' Union All
select 'GFP2017040016','IBE-12-08-1501-0011'
----------测试数据结束
--第一段
SELECT A.BillNo,MaterialId=stuff((SELECT ',' + MaterialId FROM @purBillOrderDetail AA WHERE
AA.BillNo=A.BillNo For xml path('') ),1,1,'') FROM @purBillOrderDetail A
WHERE A.BillNo='GFP2017040016'
Group by A.BillNo
--第二段
select A.BillNo, MaterialId=stuff((select ',' + B.MaterialId FROM @purBillOrderMaster AA
INNER JOIN @purBillOrderDetail BB ON BB.BillNo=AA.BillNo and AA.BillNo=A.BillNo FOR XML PATH ('')),1,1,'')
From @purBillOrderMaster A
INNER JOIN @purBillOrderDetail B ON B.BillNo=A.BillNo
WHERE A.BillNo='GFP2017040016'
Group BY A.BillNo
问题描述:
执行第二段提示需要将MaterialId也纳入分组,第一段正常
疑问:两段代码差不多,为什么第二段要将MaterialId纳入分组
如果要从表头关联表身来写,怎么才可以不用将MaterialId纳入分组
--创建订单主表
declare @purBillOrderMaster table (BillNo varchar(50))
insert into @purBillOrderMaster
select 'GFP2017040016'
--创建订单明细表
declare @purBillOrderDetail table (BillNo varchar(50),MaterialId varchar(200))
insert into @purBillOrderDetail
select 'GFP2017040016','IBE-14-01-0054-0002' Union All
select 'GFP2017040016','IBE-12-08-1501-0011'
----------测试数据结束
--第一段
SELECT A.BillNo,MaterialId=stuff((SELECT ',' + MaterialId FROM @purBillOrderDetail AA WHERE
AA.BillNo=A.BillNo For xml path('') ),1,1,'') FROM @purBillOrderDetail A
WHERE A.BillNo='GFP2017040016'
Group by A.BillNo
--第二段
select A.BillNo, MaterialId=stuff((select ',' + B.MaterialId FROM @purBillOrderMaster AA
INNER JOIN @purBillOrderDetail BB ON BB.BillNo=AA.BillNo and AA.BillNo=A.BillNo FOR XML PATH ('')),1,1,'')
From @purBillOrderMaster A
INNER JOIN @purBillOrderDetail B ON B.BillNo=A.BillNo
WHERE A.BillNo='GFP2017040016'
Group BY A.BillNo