SQL 临时表和 XML PATH

2019独角兽企业重金招聘Python工程师标准>>>

SELECT    t1.ExtendCarId ,
                    t1.Price ,
                    t1.CreateTime ,
                    t2.CarID ,
                    t2.CarName ,
                    t3.SerialID ,
                    t3.SerialName ,
                    t4.BrandID ,
                    t4.BrandName ,
                    t5.ParamItemID ,
                    t5.ParamItemName ,
                    ( SELECT    ParamItemName + ','
                      FROM      dbo.DealerExtendCarIdParamRela WITH ( NOLOCK )
                      WHERE     ExtendCarId = t1.ExtendCarId
                    FOR
                      XML PATH('')
                    ) AS ItemName
		INTO #Temp
          FROM      dbo.DealerExtendCarsInfo t1
                    INNER JOIN Base.dbo.CarBasic t2 WITH ( NOLOCK ) ON t2.CarID = t1.CarId
                    INNER JOIN Base.dbo.CarSerial t3 WITH ( NOLOCK ) ON t3.SerialID = t2.SerialID
                    INNER JOIN Base.dbo.CarBrand t4 WITH ( NOLOCK ) ON t4.BrandID = t3.BrandID
                    LEFT JOIN dbo.DealerExtendCarIdParamRela t5 WITH ( NOLOCK ) ON t1.ExtendCarId = t5.ExtendCarId
          GROUP BY  t1.ExtendCarId ,
                    t1.Price ,
                    t1.CreateTime ,
                    t2.CarID ,
                    t2.CarName ,
                    t3.SerialID ,
                    t3.SerialName ,
                    t4.BrandID ,
                    t4.BrandName ,
                    t5.ParamItemID ,
                    t5.ParamItemName
        


SELECT  B.ExtendCarId ,
        LEFT(ItemName, LEN(ItemName) - 1) AS ParamItemNames,B.ExtendCarId ,
                    B.Price ,
					B.CreateTime ,
                    B.CarID ,
                    B.CarName ,
                    B.SerialID ,
                    B.SerialName ,
                    B.BrandID ,
                    B.BrandName ,
                    B.ParamItemID ,
                    B.ParamItemName 
FROM    #Temp B
	
	
DROP TABLE #Temp

输出截图

转载于:https://my.oschina.net/CrazyBoy1024/blog/825410