SqlServer操作结果集

[阔口阔落丷]

SELECT Q.父物料编码,
Q.料件编码,
Q.子2,
Q.子3,
Q.子4,
Q.子5,
Q.子6,
Q.净耗
FROM ( SELECT
--TOP 1
c.FNUMBER AS '父物料编码' ,
d.FNUMBER AS '料件编码' ,
a1.FNUMBER AS '子2' ,
a2.FNUMBER AS '子3' ,
a3.FNUMBER AS '子4' ,
a4.FNUMBER AS '子5' ,
a5.FNUMBER AS '子6' ,
a.F_PAEZ_DECIMAL AS '净耗'
FROM T_ENG_BOM a
INNER JOIN T_ENG_BOMCHILD b ON a.FID = b.FID
LEFT JOIN T_BD_MATERIAL c ON a.FMATERIALID = c.FMATERIALID
LEFT JOIN T_BD_MATERIAL d ON b.FMATERIALID = d.FMATERIALID
LEFT JOIN ( SELECT e.FMATERIALID ,
d.FNUMBER ,
e.FID ,
f.FBOMID
FROM T_ENG_BOM e
INNER JOIN T_ENG_BOMCHILD f ON e.FID = f.FID
LEFT JOIN T_BD_MATERIAL d ON f.FMATERIALID = d.FMATERIALID
) a1 ON a1.FMATERIALID = b.FMATERIALID
LEFT JOIN ( SELECT e.FMATERIALID ,
d.FNUMBER ,
e.FID ,
FBOMID
FROM T_ENG_BOM e
INNER JOIN T_ENG_BOMCHILD f ON e.FID = f.FID
LEFT JOIN T_BD_MATERIAL d ON f.FMATERIALID = d.FMATERIALID
) a2 ON a2.FID = a1.FBOMID
LEFT JOIN ( SELECT e.FMATERIALID ,
d.FNUMBER ,
e.FID ,
FBOMID
FROM T_ENG_BOM e
INNER JOIN T_ENG_BOMCHILD f ON e.FID = f.FID
LEFT JOIN T_BD_MATERIAL d ON f.FMATERIALID = d.FMATERIALID
) a3 ON a3.FID = a2.FBOMID
LEFT JOIN ( SELECT e.FMATERIALID ,
d.FNUMBER ,
e.FID ,
FBOMID
FROM T_ENG_BOM e
INNER JOIN T_ENG_BOMCHILD f ON e.FID = f.FID
LEFT JOIN T_BD_MATERIAL d ON f.FMATERIALID = d.FMATERIALID
) a4 ON a4.FID = a3.FBOMID
LEFT JOIN ( SELECT e.FMATERIALID ,
d.FNUMBER ,
e.FID ,
FBOMID
FROM T_ENG_BOM e
INNER JOIN T_ENG_BOMCHILD f ON e.FID = f.FID
LEFT JOIN T_BD_MATERIAL d ON f.FMATERIALID = d.FMATERIALID
) a5 ON a5.FID = a4.FBOMID
WHERE c.FNUMBER = '1964042101'
) Q


上面的是sql

查询的结构为




怎么处理才可以使结果集成为




主要要实现的功能是每个物料都对应的多个料件编码，而每个料件编码可能还会有对应的料件编码，但是就只取第一个料件编码以及最后一个料件编码


急急急！！！！！！！！！！！！！！

[阔口阔落丷]

就是例如物料A是由物料B,C组成的，但是物料C又是由D,E组成的，那么查询出的结果就是父类为A，子类为B,D,E

[Dear SQL(燊)]

WITH list as(
	SELECT ASSNO='1964042101',PARTNO='1964042100S',PARTNO2='A',PARTNO3='B',PARTNO4='C',PARTNO5=NULL,PARTNO6=NULL,USEQTY=0.535 UNION ALL
	SELECT ASSNO='1964042101',PARTNO='TPK00354',PARTNO2=NULL,PARTNO3=NULL,PARTNO4=NULL,PARTNO5=NULL,PARTNO6=NULL,USEQTY=0.535 UNION ALL
	SELECT ASSNO='1964042101',PARTNO='1964042100S',PARTNO2='A1',PARTNO3='B1',PARTNO4=NULL,PARTNO5=NULL,PARTNO6=NULL,USEQTY=0.535 UNION ALL
	SELECT ASSNO='1964042101',PARTNO='1964042100S',PARTNO2='A1',PARTNO3='B2',PARTNO4=NULL,PARTNO5=NULL,PARTNO6=NULL,USEQTY=0.535 UNION ALL
	SELECT ASSNO='1964042101',PARTNO='1964042100S',PARTNO2='A',PARTNO3='B3',PARTNO4=NULL,PARTNO5=NULL,PARTNO6=NULL,USEQTY=0.535 UNION ALL
	SELECT ASSNO='1964042101',PARTNO='1964042100S',PARTNO2='A3',PARTNO3='B4',PARTNO4=NULL,PARTNO5=NULL,PARTNO6=NULL,USEQTY=0.535 UNION ALL
	SELECT ASSNO='1964042101',PARTNO='6997C03420',PARTNO2=NULL,PARTNO3=NULL,PARTNO4=NULL,PARTNO5=NULL,PARTNO6=NULL,USEQTY=0.535 
), datas as(
	select *,rid=ROW_NUMBER()over(partition by PARTNO order by(case when partno6 is not null then 6
																	when partno5 is not null then 5
																	when partno4 is not null then 4
																	when partno3 is not null then 3
																	when partno2 is not null then 2
																	else 0
																end) desc)
				/*如有同层级有二个以上用DENSE_RANK()*/
	from list
)
select *
from datas
where rid=1

[Dear SQL(燊)]

ASSNO      PARTNO      PARTNO2 PARTNO3 PARTNO4 PARTNO5     PARTNO6     USEQTY                                  rid
---------- ----------- ------- ------- ------- ----------- ----------- --------------------------------------- --------------------
1964042101 1964042100S A       B       C       NULL        NULL        0.535                                   1
1964042101 6997C03420  NULL    NULL    NULL    NULL        NULL        0.535                                   1
1964042101 TPK00354    NULL    NULL    NULL    NULL        NULL        0.535                                   1

[阔口阔落丷]

引用 2 楼 chenshanliang 的回复:

WITH list as(
	SELECT ASSNO='1964042101',PARTNO='1964042100S',PARTNO2='A',PARTNO3='B',PARTNO4='C',PARTNO5=NULL,PARTNO6=NULL,USEQTY=0.535 UNION ALL
	SELECT ASSNO='1964042101',PARTNO='TPK00354',PARTNO2=NULL,PARTNO3=NULL,PARTNO4=NULL,PARTNO5=NULL,PARTNO6=NULL,USEQTY=0.535 UNION ALL
	SELECT ASSNO='1964042101',PARTNO='1964042100S',PARTNO2='A1',PARTNO3='B1',PARTNO4=NULL,PARTNO5=NULL,PARTNO6=NULL,USEQTY=0.535 UNION ALL
	SELECT ASSNO='1964042101',PARTNO='1964042100S',PARTNO2='A1',PARTNO3='B2',PARTNO4=NULL,PARTNO5=NULL,PARTNO6=NULL,USEQTY=0.535 UNION ALL
	SELECT ASSNO='1964042101',PARTNO='1964042100S',PARTNO2='A',PARTNO3='B3',PARTNO4=NULL,PARTNO5=NULL,PARTNO6=NULL,USEQTY=0.535 UNION ALL
	SELECT ASSNO='1964042101',PARTNO='1964042100S',PARTNO2='A3',PARTNO3='B4',PARTNO4=NULL,PARTNO5=NULL,PARTNO6=NULL,USEQTY=0.535 UNION ALL
	SELECT ASSNO='1964042101',PARTNO='6997C03420',PARTNO2=NULL,PARTNO3=NULL,PARTNO4=NULL,PARTNO5=NULL,PARTNO6=NULL,USEQTY=0.535 
), datas as(
	select *,rid=ROW_NUMBER()over(partition by PARTNO order by(case when partno6 is not null then 6
																	when partno5 is not null then 5
																	when partno4 is not null then 4
																	when partno3 is not null then 3
																	when partno2 is not null then 2
																	else 0
																end) desc)
				/*如有同层级有二个以上用DENSE_RANK()*/
	from list
)
select *
from datas
where rid=1

已经自己解决了 谢谢大佬