WITH tb1 AS ( SELECT name AS '姓名' , 科目 = '语文' , 语文 AS result FROM [Temp].[dbo].[行转列] ), tb2 AS ( SELECT name AS '姓名' , 科目 = '数学' , 数学 AS result FROM [Temp].[dbo].[行转列] ), tb3 AS ( SELECT name AS '姓名' , 科目 = '物理' , 物理 AS result FROM [Temp].[dbo].[行转列] ) SELECT * FROM ( SELECT * FROM tb1 UNION ALL SELECT * FROM tb2 UNION ALL SELECT * FROM tb3 ) tb4
AS ( SELECT name AS '姓名' ,
科目 = '语文' ,
语文 AS result
FROM [Temp].[dbo].[行转列]
),
tb2
AS ( SELECT name AS '姓名' ,
科目 = '数学' ,
数学 AS result
FROM [Temp].[dbo].[行转列]
),
tb3
AS ( SELECT name AS '姓名' ,
科目 = '物理' ,
物理 AS result
FROM [Temp].[dbo].[行转列]
)
SELECT *
FROM ( SELECT *
FROM tb1
UNION ALL
SELECT *
FROM tb2
UNION ALL
SELECT *
FROM tb3
) tb4
然后,如果你想用多个with,那么语法是:with tmp as() tmp2 as()
未完待续
select * from kaoqinA尽管这跟with不大噶,但是要是不加它会提示,缺少分号!