表A
MB005,MB006,MB007,MB008,PN
113, null, null ,406,AA
113, 311 , null ,406,BB
112, null, 2190 ,406,CC
表B
MA002，MA003
113，实验品
112，原材料
311, 前处理
2190,ROHS类
406,大宗物品
如何表A显示成
MB005, MB006, MB007, MB008, PN
实验品, null, null ,大宗物品,AA
实验品, 前处理, null ,大宗物品,BB
原材料, null, ROHS类,大宗物品,CC
谢谢
------解决方案--------------------
- SQL code
SELECT A.MA003 AS MB005, B.MA003 AS MB006, C.MA003 AS MB007, D.MA003 AS MB008, PNFROM 表A LEFT OUTER JOIN 表B AS A ON 表A.MB005 = A.MA003LEFT OUTER JOIN 表B AS B ON 表A.MB005 = B.MA003LEFT OUTER JOIN 表B AS C ON 表A.MB005 = C.MA003LEFT OUTER JOIN 表B AS D ON 表A.MB005 = D.MA003
------解决方案--------------------
- SQL code
create table #表A(MB005 varchar(4),MB006 varchar(4),MB007 varchar(4),MB008 varchar(4),PN varchar(4))insert into #表Aselect '113', 'null', 'null' ,'406','AA' union allselect '113', '311' , 'null' ,'406','BB' union allselect '112', 'null', '2190' ,'406','CC' create table #表B(MB002 varchar(4),MB003 varchar(10))insert into #表Bselect '113','实验品' union allselect '112','原材料' union allselect '311', '前处理' union allselect '2190','ROHS类' union allselect '406','大宗物品'select B.MB003 MB003,C.MB003 MB006,D.MB003 MB007,E.MB003 MB008,A.PN PN from #表A A left join #表B B on A.MB005=B.MB002left join #表B C on A.MB006=C.MB002left join #表B D on A.MB007=D.MB002left join #表B E on A.MB008=E.MB002--结果实验品 NULL NULL 大宗物品 AA实验品 前处理 NULL 大宗物品 BB原材料 NULL ROHS类 大宗物品 CC--消息(3 行受影响)
------解决方案--------------------
- SQL code
--道兄正解SELECT a.ma003 AS mb005, b.ma003 AS mb006, c.ma003 AS mb007, d.ma003 AS mb008, pnFROM 表a LEFT OUTER JOIN 表b AS a ON 表a.mb005 = a.ma003 LEFT OUTER JOIN 表b AS b ON 表a.mb005 = b.ma003 LEFT OUTER JOIN 表b AS c ON 表a.mb005 = c.ma003 LEFT OUTER JOIN 表b AS d ON 表a.mb005 = d.ma003