MsSql “with”用法

CREATE TABLE #PRODUCT(id INT,NAME1 NVARCHAR(10))
CREATE TABLE #DATA_ATTRIBUTE_ABOUT_PRODUCT(id INT,type1 INT)
CREATE TABLE #DATA_ATTRIBUTE(id INT,NAME1 NVARCHAR(10),name2 NVARCHAR(10))
INSERT INTO #PRODUCT VALUES (1,'AAA')
INSERT INTO #PRODUCT VALUES (2,'BBB')
INSERT INTO #DATA_ATTRIBUTE_ABOUT_PRODUCT VALUES (1,1)
INSERT INTO #DATA_ATTRIBUTE_ABOUT_PRODUCT VALUES (1,2)
INSERT INTO #DATA_ATTRIBUTE_ABOUT_PRODUCT VALUES (1,3)
INSERT INTO #DATA_ATTRIBUTE_ABOUT_PRODUCT VALUES (2,2)
INSERT INTO #DATA_ATTRIBUTE_ABOUT_PRODUCT VALUES (2,3)

INSERT INTO #DATA_ATTRIBUTE VALUES (1,'COUNT','500')
INSERT INTO #DATA_ATTRIBUTE VALUES (2,'COLOR','RED')
INSERT INTO #DATA_ATTRIBUTE VALUES (3,'SIZE','100*200');

--SELECT a.ID,a.Name,MAX(CASE WHEN a.Name1='COUNT' then a.Name2 ELSE '' end) AS [COUNT]
--,MAX(CASE WHEN a.Name1='COLOR' then a.Name2 ELSE '' end) AS [COLOR]
--,MAX(CASE WHEN a.Name1='SIZE' then a.Name2 ELSE '' end) AS [SIZE]
--FROM(
--SELECT a.id,a.Name1 AS Name,c.Name1,c.name2
--FROM #PRODUCT a
--LEFT JOIN #DATA_ATTRIBUTE_ABOUT_PRODUCT b ON a.id = b.id
--LEFT JOIN #DATA_ATTRIBUTE c ON b.type1=c.id
--) a
--GROUP BY a.id,a.NAME;

WITH TEMPTable AS 
(
	SELECT a.id,a.Name1 AS Name,c.Name1,c.name2
	FROM #PRODUCT a
	LEFT JOIN #DATA_ATTRIBUTE_ABOUT_PRODUCT b ON a.id = b.id
	LEFT JOIN #DATA_ATTRIBUTE c ON b.type1=c.id
)
SELECT a.ID,a.Name,MAX(CASE WHEN a.Name1='COUNT' then a.Name2 ELSE '' end) AS [COUNT]
,MAX(CASE WHEN a.Name1='COLOR' then a.Name2 ELSE '' end) AS [COLOR]
,MAX(CASE WHEN a.Name1='SIZE' then a.Name2 ELSE '' end) AS [SIZE]
FROM TEMPTable a
GROUP BY a.id,a.NAME


DROP TABLE #PRODUCT
DROP TABLE #DATA_ATTRIBUTE_ABOUT_PRODUCT
DROP TABLE #DATA_ATTRIBUTE

转载于:https://www.cnblogs.com/kinpauln/archive/2011/05/24/2055755.html