T-SQL——虚拟表
0. 背景
某些时候用于测试SQL语句，快速构建临时测试数据
1. SELECT
SELECT *
FROM
(SELECT '张三' AS Name, '语文' AS Subject, 100 AS Scores) T;
--结果
Name Subject Scores
---- ------- -----------
张三 语文 100
2. CTE
WITH Temp1 AS
(
SELECT '张三' AS Name, '语文' AS Subject, 100 AS Scores
UNION ALL
SELECT '张三' AS Name, '数学' AS Subject, 90 AS Scores
UNION ALL
SELECT '张三' AS Name, '英语' AS Subject, 80 AS Scores
UNION ALL
SELECT '李四' AS Name, '语文' AS Subject, 90 AS Scores
UNION ALL
SELECT '李四' AS Name, '数学' AS Subject, 70 AS Scores
UNION ALL
SELECT '李四' AS Name, '英语' AS Subject, 60 AS Scores
)
SELECT * FROM Temp1
--结果
Name Subject Scores
---- ------- -----------
张三 语文 100
张三 数学 90
张三 英语 80
李四 语文 90
李四 数学 70
李四 英语 60
3. VALUES
示例1
SELECT *
FROM
(
VALUES
('张三', '八年级二班', '18'),
('李四', '三年级二班', '18'),
('王五', '三年级二班', '18')
) AS T (Name, Class, Age);
--结果
Name Class Age
---- ---------- ----
张三 八年级二班 18
李四 三年级二班 18
王五 三年级二班 18
SELECT *
FROM
(
VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9)
) AS T (Num);
结果
Num
-----------
1
2
3
4
5
6
7
8
9
3. 使用场景
比如说我们需要按照某个关系对某个字段进行替换，我们常用CASE
WITH Temp AS
(
SELECT 'Tom' AS Name ,14 AS Age ,1 AS Grade
UNION ALL
SELECT 'Bob' AS Name ,15 AS Age ,2 AS Grade
UNION ALL
SELECT 'Jery' AS Name,16 AS Age,3 AS Grade
)
SELECT Name,Age,
CASE Grade
WHEN 1 THEN '一年级'
WHEN 2 THEN '二年级'
WHEN 3 THEN '三年级'
ELSE 'NULL'
END AS Grade
FROM Temp
结果：
Name Age Grade
---- ----- ------
Tom 14 一年级
Bob 15 二年级
Jery 16 三年级
但是我们可以使用VALUES和CTE快速构建一个临时的转换表，之后在通过连接查询进行转换，如下：
--这是测试数据
WITH Temp1 AS
(
SELECT 'Tom' AS Name ,14 AS Age ,1 AS Grade
UNION ALL
SELECT 'Bob' AS Name ,15 AS Age ,2 AS Grade
UNION ALL
SELECT 'Jery' AS Name,16 AS Age,3 AS Grade
)
--这是构建的一个临时转换表
,Temp2 AS (
SELECT * FROM (VALUES('1','一年级'),('2','二年级'),('3','三年级')) AS T(Grade1,Grade2)
)
SELECT * FROM Temp1 LEFT JOIN Temp2 ON Temp1.Grade=Temp2.Grade1
Name Age Grade Grade1 Grade2
---- ----------- ----------- ------ ------
Tom 14 1 1 一年级
Bob 15 2 2 二年级
Jery 16 3 3 三年级
