T-SQL——构建临时测试数据集

• 0. 背景
• 1. SELECT
• 2. CTE
• 3. VALUES

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