数据库—小知识：STUFF函数

数据库

小知识：STUFF函数

–数据库查询 15秒
SELECT
(SELECT ISNULL(STUFF((SELECT ‘;’+F_PAEZ_Text1 FROM T_Cust_MoldSeriesEntry MO
LEFT JOIN PAEZ_t_Cust_Entry100027 SE ON SE.FID = MO.FMOIDSERIES AND MO.FMATERIALID = MA.FMATERIALID FOR XML PATH(’’)),1,1,’’),’’)) 系列代码,
(SELECT ISNULL(STUFF((SELECT ‘;’+FNUMBER FROM T_Cust_MoldTypeEntry MO
LEFT JOIN PAEZ_t_Cust_Entry100029 SE ON SE.FID = MO.FTYPECODE AND MO.FMATERIALID = MA.FMATERIALID FOR XML PATH(’’)),1,1,’’),’’)) 机型代码
FROM T_BD_MATERIAL MA
LEFT JOIN T_ORG_ORGANIZATIONS_L ORGL ON ORGL.FORGID = MA.FUSEORGID
LEFT JOIN T_BD_MATERIALGROUP_L MGL ON MGL.FID = MA.FMATERIALGROUP1
WHERE FUSEORGID IN (1065512,1065513) AND FMaterialGroup1 != 0
ORDER BY ORGL.FNAME desc

–数据库查询 1秒
SELECT
(SELECT ISNULL(STUFF((SELECT ‘;’+F_PAEZ_Text1 FROM T_Cust_MoldSeriesEntry MO
LEFT JOIN PAEZ_t_Cust_Entry100027 SE ON SE.FID = MO.FMOIDSERIES WHERE MO.FMATERIALID = MA.FMATERIALID FOR XML PATH(’’)),1,1,’’),’’)) 系列代码,
(SELECT ISNULL(STUFF((SELECT ‘;’+FNUMBER FROM T_Cust_MoldTypeEntry MO
LEFT JOIN PAEZ_t_Cust_Entry100029 SE ON SE.FID = MO.FTYPECODE WHERE MO.FMATERIALID = MA.FMATERIALID FOR XML PATH(’’)),1,1,’’),’’)) 机型代码
FROM T_BD_MATERIAL MA
LEFT JOIN T_ORG_ORGANIZATIONS_L ORGL ON ORGL.FORGID = MA.FUSEORGID
LEFT JOIN T_BD_MATERIALGROUP_L MGL ON MGL.FID = MA.FMATERIALGROUP1
WHERE FUSEORGID IN (1065512,1065513) AND FMaterialGroup1 != 0
ORDER BY ORGL.FNAME desc