T-SQL——虚拟表

目录

• 0. 背景
• 1. SELECT
• 2. CTE
• 3. VALUES
• 3. 使用场景

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      三年级