两表关联汇总

snlixing 2019-03-08 11:14:29


如上图所示,两个表主料表和辅料表,得到一个对应产品的汇总表,主辅料的名称需要动态获取才行


主料表
ID 产品编号 物料名称 物料型号 数量
1 PN001 螺母 L01 50
2 PN001 螺母 L02 50
3 PN001 螺圈 100
4 PN002 螺母 L01 50
5 PN002 螺杆 50
6 PN003 螺母 L02 10

辅料表
ID 产品编号 辅料名称 辅料尺寸 数量
1 PN001 标签 7x5 100
2 PN001 纸箱 20x20x12 5
3 PN002 标签 7x5 50
4 PN004 皮筋 50

得到如下表格:
产品编号 所需主料 所需辅料
螺母 螺圈 螺杆 标签 纸箱 皮筋
PN001 100 100 100 5
PN002 50 50 50
PN003 10
PN004 50
...全文
167 6 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
Dear SQL(燊) 2019-03-08
  • 打赏
  • 举报
回复
if object_id('tempdb..#list') is not null drop table #list
;
with t1 as(			
	select ID=1,产品编号='PN001',物料名称='螺母',物料型号='L01',数量=50 UNION ALL
	select ID=2,产品编号='PN001',物料名称='螺母',物料型号='L02',数量=50 UNION ALL
	select ID=3,产品编号='PN001',物料名称='螺圈',物料型号='',数量=100   UNION ALL
	select ID=4,产品编号='PN002',物料名称='螺母',物料型号='L01',数量=50 UNION ALL
	select ID=5,产品编号='PN002',物料名称='螺杆',物料型号='',数量=50    UNION ALL
	select ID=6,产品编号='PN003',物料名称='螺母',物料型号='L02',数量=10 
),t2 as(
				
	select ID=1,产品编号='PN001',辅料名称='标签',辅料尺寸='7x5'		,数量=100 union all
	select ID=2,产品编号='PN001',辅料名称='纸箱',辅料尺寸='20x20x12'	,数量=5   union all
	select ID=3,产品编号='PN002',辅料名称='标签',辅料尺寸='7x5'		,数量=50  union all
	select ID=4,产品编号='PN004',辅料名称='皮筋',辅料尺寸=''			,数量=50 

)
select 产品编号,物料名称,数量 into #list
from t1
union all
select 产品编号,物料名称=辅料名称,数量
from t2

declare @sql nvarchar(max),@colname nvarchar(4000)=''
;
with list as(
	select distinct 物料名称
	from #list
)
select @colname=@colname+',['+物料名称+']'
from list


set @sql='

select *
from #list lt pivot (sum(数量) for 物料名称 in('+RIGHT(@colname,len(@colname)-1)+')) pt

'
exec(@sql)

/*
产品编号  标签          螺杆          螺母          螺圈          皮筋          纸箱
----- ----------- ----------- ----------- ----------- ----------- -----------
PN001 100         NULL        100         100         NULL        5
PN002 50          50          50          NULL        NULL        NULL
PN003 NULL        NULL        10          NULL        NULL        NULL
PN004 NULL        NULL        NULL        NULL        50          NULL

(4 行受影响)

*/

snlixing 2019-03-08
  • 打赏
  • 举报
回复
动态生成的怎么改呢
snlixing 2019-03-08
  • 打赏
  • 举报
回复
引用 3 楼 Dear SQL 的回复:
;
with t1 as(
select ID=1,产品编号='PN001',物料名称='螺母',物料型号='L01',数量=50 UNION ALL
select ID=2,产品编号='PN001',物料名称='螺母',物料型号='L02',数量=50 UNION ALL
select ID=3,产品编号='PN001',物料名称='螺圈',物料型号='',数量=100 UNION ALL
select ID=4,产品编号='PN002',物料名称='螺母',物料型号='L01',数量=50 UNION ALL
select ID=5,产品编号='PN002',物料名称='螺杆',物料型号='',数量=50 UNION ALL
select ID=6,产品编号='PN003',物料名称='螺母',物料型号='L02',数量=10
),t2 as(

select ID=1,产品编号='PN001',辅料名称='标签',辅料尺寸='7x5' ,数量=100 union all
select ID=2,产品编号='PN001',辅料名称='纸箱',辅料尺寸='20x20x12' ,数量=5 union all
select ID=3,产品编号='PN002',辅料名称='标签',辅料尺寸='7x5' ,数量=50 union all
select ID=4,产品编号='PN004',辅料名称='皮筋',辅料尺寸='' ,数量=50

),list as(
select 产品编号,物料名称,数量
from t1
union all
select 产品编号,物料名称=辅料名称,数量
from t2
)
select *
from list lt pivot (sum(数量) for 物料名称 in([螺母],[螺圈],[螺杆],[标签],[纸箱],[皮筋])) pt

/*
产品编号 螺母 螺圈 螺杆 标签 纸箱 皮筋
----- ----------- ----------- ----------- ----------- ----------- -----------
PN001 100 100 NULL 100 5 NULL
PN002 50 NULL 50 50 NULL NULL
PN003 10 NULL NULL NULL NULL NULL
PN004 NULL NULL NULL NULL NULL 50

(4 行受影响)
*/


谢谢大神,实际应用中[螺母],[螺圈],[螺杆],[标签],[纸箱 皮筋]都是动态的,不是直接写上的呢
Dear SQL(燊) 2019-03-08
  • 打赏
  • 举报
回复
;
with t1 as(			
	select ID=1,产品编号='PN001',物料名称='螺母',物料型号='L01',数量=50 UNION ALL
	select ID=2,产品编号='PN001',物料名称='螺母',物料型号='L02',数量=50 UNION ALL
	select ID=3,产品编号='PN001',物料名称='螺圈',物料型号='',数量=100   UNION ALL
	select ID=4,产品编号='PN002',物料名称='螺母',物料型号='L01',数量=50 UNION ALL
	select ID=5,产品编号='PN002',物料名称='螺杆',物料型号='',数量=50    UNION ALL
	select ID=6,产品编号='PN003',物料名称='螺母',物料型号='L02',数量=10 
),t2 as(
				
	select ID=1,产品编号='PN001',辅料名称='标签',辅料尺寸='7x5'		,数量=100 union all
	select ID=2,产品编号='PN001',辅料名称='纸箱',辅料尺寸='20x20x12'	,数量=5   union all
	select ID=3,产品编号='PN002',辅料名称='标签',辅料尺寸='7x5'		,数量=50  union all
	select ID=4,产品编号='PN004',辅料名称='皮筋',辅料尺寸=''			,数量=50 

),list as(
	select 产品编号,物料名称,数量
	from t1
	union all
	select 产品编号,物料名称=辅料名称,数量
	from t2
)
select *
from list lt pivot (sum(数量) for 物料名称 in([螺母],[螺圈],[螺杆],[标签],[纸箱],[皮筋])) pt

/*
产品编号  螺母          螺圈          螺杆          标签          纸箱          皮筋
----- ----------- ----------- ----------- ----------- ----------- -----------
PN001 100         100         NULL        100         5           NULL
PN002 50          NULL        50          50          NULL        NULL
PN003 10          NULL        NULL        NULL        NULL        NULL
PN004 NULL        NULL        NULL        NULL        NULL        50

(4 行受影响)
*/
Dear SQL(燊) 2019-03-08
  • 打赏
  • 举报
回复
;
with t1 as(			
	select ID=1,产品编号='PN001',物料名称='螺母',物料型号='L01',数量=50 UNION ALL
	select ID=2,产品编号='PN001',物料名称='螺母',物料型号='L02',数量=50 UNION ALL
	select ID=3,产品编号='PN001',物料名称='螺圈',物料型号='',数量=100   UNION ALL
	select ID=4,产品编号='PN002',物料名称='螺母',物料型号='L01',数量=50 UNION ALL
	select ID=5,产品编号='PN002',物料名称='螺杆',物料型号='',数量=50    UNION ALL
	select ID=6,产品编号='PN003',物料名称='螺母',物料型号='L02',数量=10 
),t2 as(
				
	select ID=1,产品编号='PN001',辅料名称='标签',辅料尺寸='7x5'		,数量=100 union all
	select ID=2,产品编号='PN001',辅料名称='纸箱',辅料尺寸='20x20x12'	,数量=5   union all
	select ID=3,产品编号='PN002',辅料名称='标签',辅料尺寸='7x5'		,数量=50  union all
	select ID=4,产品编号='PN004',辅料名称='皮筋',辅料尺寸=''			,数量=50 

),list as(
	select 产品编号,物料名称,数量
	from t1
	union all
	select 产品编号,物料名称=辅料名称,数量
	from t2
)
select *
from list lt pivot (sum(数量) for 物料名称 in([螺母],[螺圈],[螺杆],[标签],[纸箱	皮筋])) pt

/*
产品编号  螺母          螺圈          螺杆          标签          纸箱	皮筋
----- ----------- ----------- ----------- ----------- -----------
PN001 100         100         NULL        100         NULL
PN002 50          NULL        50          50          NULL
PN003 10          NULL        NULL        NULL        NULL
PN004 NULL        NULL        NULL        NULL        NULL

(4 行受影响)
*/
snlixing 2019-03-08
  • 打赏
  • 举报
回复
大神们,这个怎么写呢

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧