平时写SQL最烦的就是想验证一个查询逻辑结果发现手头连一张能用的测试表都没有。建表要权限插数据要写一堆INSERT测完还得清理麻烦得很。所以我很早之前就养成了一个习惯用VALUES直接生成一张带数据的临时表把测试数据、字典表、映射关系当场“造”出来用完就走不污染数据库。这个写法学名叫做表值构造器Table Value Constructor在SQL Server、MySQL、PostgreSQL这些主流数据库里都能用。它的核心价值就一句话把一组常量直接变成一张可以被SELECT、JOIN、UNION的结果集本质上是把“造数据”这件事从DML操作改成了纯查询操作。不管你是写报表的、做数据分析的、还是维护业务系统的开发这招都能帮你省下大量时间。接下来我把这套用法从头到尾拆一遍。1. 先看场景到底什么时候需要“用VALUES造临时表”1.1 最典型的三个业务场景第一个场景是测试SQL逻辑。比如你刚写了一个复杂的多表JOIN或者是带窗口函数的分析SQL你并不确定结果对不对。这时候最靠谱的办法不是直接往生产环境跑而是先构造一小撮和真实数据形状接近的数据放到临时表里跑一遍看看输出是否符合预期。用VALUES造数据比CREATE TABLE再逐行INSERT快得多尤其适合三五行的数据验证。第二个场景是给查询补一个“静态字典”。业务里常有这种需求原始表里存的是代码值比如星期几存1到7、状态存0/1/2但报表上要显示中文名。你不想改表也没权限建一张字典表这时候用VALUES临时建一张两列的映射表跟原表做LEFT JOIN问题就解决了。我在写日报、周报类SQL时经常这么干省掉了一张又一张物理字典表。第三个场景是批量数据比对。有时候你要把外部系统导出的数据跟库里已有数据做差异比对。外部数据量不大几十行上百行不值得落一张表。我一般直接把外部数据整理成VALUES列表用EXCEPT或者NOT EXISTS去比比导入临时表再比对要快很多尤其是在只有查询权限的环境里这几乎是唯一的解法。1.2 为什么不直接建表INSERT非要绕一圈有人会问临时表嘛CREATE TABLE #tmp然后INSERT几行进去不也很快确实也能干但VALUES方案有几个实打实的优势。第一是权限门槛低。很多数据仓库环境里开发账号只有查询权限CREATE TABLE都执行不了。SELECT FROM (VALUES ...)这种写法本质上只是查询权限要求是最低那一档。第二是作用域干净。临时表好歹要在会话里存活连接复用的时候还得记得清理VALUES造出来的结果在语句执行完就没了不存在残留问题。第三是代码可读性高。几个人一起Review代码的时候一眼就能看到这张“临时表”里是什么数据比翻到几百行之外找INSERT语句强多了。当然这个方案也有明显的边界不适合造大数据量。你不可能用VALUES手写一万行数据那纯粹是自虐。它适合的数据量级是几行到几百行。数据量一旦过千老老实实用临时表或者用生成序列的写法去填充。2. VALUES为什么能“当表用”背后的逻辑与三种主流写法2.1 表值构造器的原理VALUES看起来就是一组括号但你得理解它在SQL语法树里是一个“行集构造器”。每一对括号代表一行记录括号里的每个值代表一列。当你写 FROM (VALUES (1,a),(2,b)) AS t(id,name) 的时候SQL引擎会把这堆字面量组合成一张虚拟的两行两列表列名由AS子句指定行数据就是括号里那几行。这里有个关键点这张“表”在查询计划里跟真实表没有本质区别只是数据来自常量而不是磁盘扫描。所以你可以在它上面做任何表能做的操作——过滤、聚合、排序、JOIN、UNION完全不受限制。理解这一点之后你会发现它其实就是一个不用CREATE TABLE的即时表。2.2 写法一SELECT INTO VALUES 创建真正的临时表如果你确实需要一个物理临时表用来分步骤处理数据可以用SELECT INTO把VALUES结果直接灌进临时表。SQL Server的写法是这样的SELECT * INTO #temp_sales FROM (VALUES (1, 2024-06-01, 100.50), (2, 2024-06-02, 200.00), (3, 2024-06-03, 150.25) ) AS t(id, sale_date, amount); SELECT * FROM #temp_sales;这段SQL在SQL Server里执行后会自动根据VALUES推断列的类型创建一个名为#temp_sales的临时表并插入数据。#开头表示本地临时表只在当前会话可见会话一断自动删除。这种写法的好处是后续可以对这个临时表做加索引、更新、重复查询等操作而不用反复写VALUES。MySQL 8.0的写法基本一致但细节上有差异后面章节会专门说。PostgreSQL则完全兼容这套语法直接跑就行。2.3 写法二派生表直接参与查询不留任何痕迹大部分时候我们不需要真的建临时表只需要在一条语句里用到那批数据。这时候就不用SELECT INTO了直接把VALUES当成派生表放进FROM子句里参与JOIN或者查询。举个例子我们想统计几个指定门店在订单表里的销售额门店信息不在维表里而是就这么几个SELECT t.store_id, SUM(o.amount) AS total_amount FROM (VALUES (101, 上海静安店), (102, 北京朝阳店), (103, 广州天河店) ) AS t(store_id, store_name) LEFT JOIN orders o ON o.store_id t.store_id GROUP BY t.store_id, t.store_name;这种写法的好处是语句结束即释放连临时表的生命周期都不用管理。它在执行计划里就是一次常量扫描加一次JOIN性能上没有任何负担。我做数据校验的时候特别喜欢这种写法因为整段SQL是自包含的贴给同事就能跑不依赖任何前置建表脚本。2.4 写法三CTE VALUES 做递归或分步计算第三种常见用法是把VALUES放进公共表表达式CTE里尤其是在递归查询里用来做种子数据。比如构造一个从今天开始连续30天的日期序列WITH RECURSIVE date_seq AS ( SELECT CAST(2024-06-01 AS DATE) AS d UNION ALL SELECT DATEADD(DAY, 1, d) FROM date_seq WHERE d 2024-06-30 ) SELECT d FROM date_seq;这个例子里种子数据只有一行用VALUES显得多余但当你需要多个种子行的时候VALUES就派上用场了WITH RECURSIVE base(id, start_date) AS ( SELECT * FROM (VALUES (1, 2024-01-01), (2, 2024-02-01), (3, 2024-03-01) ) AS t(id, start_date) UNION ALL SELECT id, DATEADD(MONTH, 1, start_date) FROM base WHERE start_date 2024-12-01 ) SELECT * FROM base;这种写法在做日历表、补全日期序列、生成报表时间轴时非常实用。CTE的递归部分负责按规则生成数据VALUES负责提供起点两者配合得严丝合缝。3. 数据库方言差异一个语法各家写法不同3.1 SQL Server 里的用法与注意事项SQL Server从2008版本开始支持表值构造器也就是VALUES后面跟多行这种语法。用起来最顺手写法就是前面展示的两种。需要注意的一个点是SQL Server对列名推断有自己的规则。如果VALUES里面全是数字列类型会被推断成int如果你插入100.5就会变成decimal。NULL值比较特殊它会被推断为int这有时候会导致后续运算报错。还有一个细节如果一个VALUES列表里混了int和varcharSQL Server会尽量做隐式转换。比如 (1, abc) 和 (2, 200) 混在一个列表里200会被转成200列类型变成varchar。这种隐式行为在大部分场景下没问题但如果你需要精确控制类型最好在SELECT外面包一层CAST。我在做数据合并的时候吃过亏VALUES里写了个NULL后来跟日期列UNION时一直报转换错误排查了半天才发现是类型推断搞的鬼。3.2 MySQL 8.0 的 ROW 写法MySQL 8.0.19版本开始支持ROW构造器和VALUES语句。写法上跟SQL Server有点不一样最大的坑是派生表必须要有别名否则直接报错。看示例SELECT * FROM (VALUES ROW(1, 苹果), ROW(2, 香蕉), ROW(3, 梨) ) AS t(id, name);注意关键字是ROW不是直接在括号里写。而且MySQL对表别名是强制的AS t(id, name) 这部分的表别名t绝对不能省列别名的定义方式和SQL Server一致。如果你不定义列名MySQL默认给列起名叫做column_0、column_1虽然能用但可读性差建议还是显式指定列名。还有一点MySQL的VALUES语法在部分版本里要求每条ROW里的列数完全一致这跟SQL Server一样。不一致会直接报错。另外别把这种VALUES语法跟MySQL的INSERT INTO ... VALUES混淆一个是查询一个是写操作。3.3 PostgreSQL 最自由VALUES还能独立执行PostgreSQL对VALUES的支持最为彻底。它不光能当派生表还能直接作为独立语句执行不加SELECT也能跑VALUES (1, 苹果), (2, 香蕉);这样会直接返回两行两列的结果集列名默认叫column1、column2。这在很多数据库里做不到是PostgreSQL特有的便利性。当派生表使用时写法和SQL Server一致SELECT * FROM (VALUES (1, 苹果), (2, 香蕉) ) AS t(id, name);PostgreSQL还有一个隐藏优势VALUES列表支持ORDER BY和LIMIT。也就是说VALUES生成的数据可以直接排序和分页这在很多数据库里是不行的。实际工作中我经常用这个特性做一个快速的参数列表然后分页处理。3.4 Oracle老版本别玩新版才支持Oracle的情况比较特殊。Oracle 23ai之前SQL语法里没有独立的VALUES表构造器。如果你拿到老的Oracle环境想用VALUES造临时数据只能用另外两种土办法。第一种是用UNION ALL拼SELECT 1 AS id, 苹果 AS name FROM DUAL UNION ALL SELECT 2, 香蕉 FROM DUAL UNION ALL SELECT 3, 梨 FROM DUAL;第二种是用Oracle的集合函数配合TABLE操作符比如构造一个多行多列的数据集。这种写法一是冗长二是嵌套层级多了很难读。所以如果你还在维护老Oracle库VALUES就暂时用不了认命用UNION ALL吧。Oracle 23ai开始内置了对VALUES语句的支持写法跟PostgreSQL的独立VALUES类似新项目可以尝试。3.5 其他数据库的兼容情况Hive、Doris、ClickHouse这些分析型数据库对新语法都比较积极。Hive从0.13版本开始支持 VALUES (1, a) AS (col1, col2) 的写法但列别名必须加在AS后面。Doris和StarRocks基本兼容MySQL的写法。ClickHouse比较特殊它更推荐用SELECT ... UNION ALL或者用arrayJoin函数来生成序列VALUES支持有限。如果你在多个数据库之间切换建议先查一下目标版本的官方文档别默认所有库都一个写法。我整理了一张简表方便你对照数据库VALUES当派生表独立执行是否强制表别名备注SQL Server 2008支持不支持建议写类型推断需注意MySQL 8.0.19支持不支持强制必须用ROW()构造PostgreSQL支持支持建议写支持ORDER BY/LIMITOracle 23ai支持支持需按文档老版本用UNION ALLHive 0.13支持不支持强制写法略有差异ClickHouse有限支持不支持—推荐UNION ALL4. 实操演练三个拿来即用的临时表案例4.1 案例一快速构造一批测试订单灌入临时表先说一个最常见的需求你正在测试一条聚合统计SQL想模拟几张订单数据。用VALUES造十行订单数据放进临时表然后跑统计逻辑SELECT * INTO #orders_test FROM (VALUES (1001, 101, 2024-06-01, 99.90, 已支付), (1002, 102, 2024-06-01, 199.00, 已支付), (1003, 101, 2024-06-02, 59.90, 已退款), (1004, 103, 2024-06-02, 299.00, 已支付), (1005, 101, 2024-06-03, 129.00, 未支付), (1006, 102, 2024-06-03, 89.00, 已支付), (1007, 103, 2024-06-03, 249.00, 已支付), (1008, 101, 2024-06-04, 19.90, 已退款), (1009, 102, 2024-06-04, 499.00, 已支付), (1010, 103, 2024-06-05, 159.00, 已支付) ) AS t(order_id, store_id, order_date, amount, status);拿到这个#orders_test临时表之后就可以随便折腾了。比如按门店汇总销售额SELECT store_id, COUNT(*) AS order_cnt, SUM(amount) AS total_amount, AVG(amount) AS avg_amount FROM #orders_test WHERE status 已支付 GROUP BY store_id;这套流程用来验证业务口径很快。比如你想确认“已支付”和“已退款”分别怎么影响报表只要改WHERE条件就能立刻看到结果。VALUES造数唯一的烦恼是手写数据要细心字段含义自己得门清别把日期写进数字列里。4.2 案例二不建表直接JOIN星期字典报表开发里最经典的一个痛点是日期字段存的是星期几的数字1代表周一7代表周日但报表前端想展示“周一”“周二”这种中文。最笨的办法是写CASE WHEN但每个要用的地方都写一遍CASE WHEN代码又臭又长。用VALUES做一个临时的星期字典再LEFT JOIN上去代码清爽许多SELECT a.order_date, w.weekday_name, COUNT(*) AS order_cnt FROM orders a LEFT JOIN (VALUES (1, 周一), (2, 周二), (3, 周三), (4, 周四), (5, 周五), (6, 周六), (7, 周日) ) AS w(weekday_no, weekday_name) ON w.weekday_no DATEPART(WEEKDAY, a.order_date) GROUP BY a.order_date, w.weekday_name;这段代码在SQL Server里跑没问题。需要说明的是不同数据库获取星期几的函数不一样SQL Server的DATEPART(WEEKDAY, date)默认按周日为一周的第一天所以它的结果1代表周日2代表周一以此类推。刚才这个例子就要根据实际情况调整映射如果你希望周一是数字1那么用DATEPART之后要做个取模变换。这就是经验之谈——很多新手在这里照着网上抄结果星期一显示成了“周二”因为没注意DATEPART的语义。调整方法很简单在SQL Server里用 ((DATEPART(WEEKDAY, date) DATEFIRST - 2) % 7) 1 这种方式把周一强制转换成1这样再JOIN字典就能保证对应关系正确。VALUES字典只是我们的目标映射表源数据那边要统一好口径两边的数字分别代表什么必须先对齐。4.3 案例三用VALUES配合递归生成连续日期序列做报表时间轴填充时经常遇到某些天没有业务数据但报表要求必须显示连续日期没数据的日期补0。这时候就需要先生成一段日期序列再LEFT JOIN业务数据。日期序列可以用递归CTE生成而种子数据可以直接用VALUES。下面这个例子生成2024年6月的完整日期列表然后左连接订单表。没有订单的日期销售金额显示0保证报表日期连续WITH date_list AS ( SELECT CAST(2024-06-01 AS DATE) AS d UNION ALL SELECT DATEADD(DAY, 1, d) FROM date_list WHERE d 2024-06-30 ) SELECT d.d AS report_date, ISNULL(SUM(o.amount), 0) AS daily_amount FROM date_list d LEFT JOIN orders o ON o.order_date d.d GROUP BY d.d ORDER BY d.d;这里其实没有直接用到VALUES不过如果种子数据是多条VALUES的用武之地就来了。比如你要生成多组序列每个门店从不同起始日开始连续30天WITH store_seed AS ( SELECT * FROM (VALUES (101, 2024-06-01), (102, 2024-06-05), (103, 2024-06-10) ) AS t(store_id, start_date) ), date_expanded AS ( SELECT store_id, CAST(start_date AS DATE) AS d FROM store_seed UNION ALL SELECT store_id, DATEADD(DAY, 1, d) FROM date_expanded WHERE d DATEADD(DAY, 29, CAST((SELECT start_date FROM store_seed WHERE store_id date_expanded.store_id) AS DATE)) ) SELECT * FROM date_expanded;这段递归的逻辑稍微有点绕简单说就是每个门店从自己的起始日开始连续生成30天记录用来做门店维度的全量日期底表。跑完之后左连业务数据每个门店每天的销售情况一目了然日期不会断层。这是我在做门店经营日报时反复用到的模板。5. 常见报错与避坑指南5.1 最容易踩的坑汇总VALUES语法看着简单但放在不同数据库里执行报错时真能把人气笑。我把自己踩过的坑和同事群里高频出现的问题统一整理一下。第一个坑是忘记给派生表起别名。这在SQL Server、MySQL、PostgreSQL里都是高频错误。MySQL尤其严格没别名直接报“Every derived table must have its own alias”。SQL Server虽然容忍无别名的情况较少但最好养成习惯写完VALUES后面的闭括号立刻补上AS t(...)。第二个坑是VALUES里的列数不统一。比如第一行写了两个值第二行写了三个值数据库直接报“列数与值数不匹配”。这种错误主要是手滑复制粘贴的时候多写了个逗号或者少写了个列。排查方法也很简单数括号里的逗号数量第一行几个值后面每一行必须一模一样。第三个坑是数据类型推断带来的隐藏问题。注意看这个例子SELECT * FROM (VALUES (1, 2024-06-01), (2, NULL) ) AS t(id, dt);第二行第二列的NULL会被推断为int不是日期。如果你后续拿这个dt列去跟日期比较或转换就会报转换失败。解决方法是显式做CASTSELECT * FROM (VALUES (1, CAST(2024-06-01 AS DATE)), (2, CAST(NULL AS DATE)) ) AS t(id, dt);这招非常关键做数据合并或者UNION时类型不确定几乎必出问题。第四个坑是字符串长度被截断。VALUES推断varchar长度时会参考第一行的长度比如第一行写苹果第二行写榴莲千层蛋糕在SQL Server里列类型可能被推断为varchar(4)后面的数据就被截断了。虽然看起来不报错但数据已经错了。稳妥起见字符串列也建议显式CAST成varchar(N)N取你想要的最大长度。第五个坑是超大VALUES列表降低性能。我之前见过有人把几千行数据手工整理成VALUES拼进SQL里查询卡到怀疑人生。优化器对常量列表的处理有时候并不高效数据量超过几百行就该考虑用临时表或导入工具了。VALUES适合小数据量不是万能的。5.2 常见报错速查表报错信息大概率原因解决方案Every derived table must have its own aliasMySQL中VALUES派生表没写表别名在闭括号后补上 AS tIncorrect syntax near ,VALUES列表列数不一致或逗号格式错误检查每行列数是否一致The multi-part identifier could not be bound别名写错或列名不在该派生表中确认AS t(col1,col2)里的列名正确Conversion failed when converting date and/or time from character string字符串与目标类型不匹配给对应列显式CAST成日期类型Operand should contain 1 column(s)子查询SELECT的列数超过1列检查子查询的SELECT列数Column name or number of supplied values does not match table definitionINSERT时列数与VALUES列数不一致逐列对齐INSERT的目标列String or binary data would be truncated字符串内容超过推断的varchar长度显式CAST成更长的varcharVALUES clause is not supported in this version数据库版本太老或方言不支持改用UNION ALL或升级版本5.3 排查技巧一条SQL把问题定位清楚遇到复杂一点的数据构造需求我习惯先在本地用最小例子验证语法。比如想用VALUES构造三行的用户表我第一步先只SELECT不带任何JOIN和计算SELECT * FROM (VALUES (1, 张三, 25), (2, 李四, 30) ) AS t(id, name, age);如果这条跑通了再逐步往上加WHERE、JOIN、GROUP BY。如果直接写完整版SQL一旦出错你很难判断是VALUES本身的问题还是后续逻辑的问题。这个排查思路干多了自然懂但新手容易上来就写一大坨报错之后整个人都懵了。另一个实用技巧是善用“只查询不落表”的方式来验证VALUES里的数据是否符合预期。有时候你怀疑数据造错了但临时表已经建好查一下就知道。比如SELECT * FROM #orders_test ORDER BY order_id;确认数据没问题再跑聚合逻辑。别省这一步验证VALUES手写的数据出错的概率比想象中高我自己写错金额、写错日期是家常便饭。写在最后的一个实践建议用了这么多年VALUES我觉得它最大的价值不是炫技而是把“临时数据”这个问题的处理门槛降低到了零。以前要造一份测试数据得先想清楚要建什么表、有没有权限、用完怎么清理现在一条SQL就完事了。尤其是在那些只能跑SELECT的分析环境里VALUES几乎是唯一的造数手段。我个人在实际项目里的习惯是凡是数据量在几十行以内、只服务一次查询或一个存储过程的临时映射一律用VALUES超过这个量级才考虑建物理临时表。每一次造数都顺手加上显式的类型转换不为省那点字数牺牲稳定性。如果你以后碰到“手头有数据但没表”的尴尬场景不妨先想想能不能用VALUES把问题解决掉。能就用它。不能再回头建表。这个小习惯能帮你省下大量无意义的建表、授权、清理流程把精力留在真正需要思考的SQL逻辑上。