冷门SQL技能之行内数据表

定义

行内数据表(inline table)，也就是把数据表的结构和数据都写在代码上，而不需要存储在数据库引擎。

适用场景

简单来说就是有些小量的数据，不值得独立建一张表来管理，但某个代码里面又需要，我们就可以直接把它写在代码上。比如下面这些场景：

• 临时跑一些SQL实验，我们不想创建一个新表或者不具有建表权限。
• 运算过程中的一些常量数据，没有复用场景，而且用表的形式更方便计算。
• 替代冗长的case语句

示例

使用union实现

好处是union是个常用操作，比较容易记忆。缺点是不太优雅。

SELECT *
FROM (
    SELECT 1 AS id, 'red' as value
    UNION ALL
    SELECT 2, 'orange'
    UNION ALL
    SELECT 5, 'yellow'
    UNION ALL
    SELECT 10, 'green'
    UNION ALL
    SELECT 11, 'blue'
) AS colors;

使用values实现

我们知道values常用于insert多行的操作，所以values可以定义多行数据。这里只需要用括号括起来，再用AS来指定表名和字段即可。特别是指定字段这部分，的确是非常冷门的技能了，一时不容易想起。优点是代码更优雅。

--单纯的数据表
SELECT *
FROM ( VALUES 
    (1, 'red'),
    (2, 'orange'),
    (5, 'yellow'),
    (10, 'green'),
    (11, 'blue'),
    (12, 'indigo'),
    (20, 'violet')
) AS colors(id, value);

--参加join的例子
SELECT table1.*, colors.value
FROM table1
LEFT JOIN ( VALUES
    (1, 'red'),
    (2, 'orange'),
    (5, 'yellow'),
    (10, 'green'),
    (11, 'blue'),
    (12, 'indigo'),
    (20, 'violet')
) AS colors(id, value)
ON Colors.id = table1.color_id;