做数据库开发的人应该都有过这种体验报表脚本里需要临时把一批状态码翻译成中文或者需要临时定义一组层级关系参与JOIN但建一张实体表又觉得没必要用CASE WHEN反复写又长又丑。其实 SQL 早就给了一个很优雅的解法——直接用VALUES子句生成一张“带数据的临时表”。这个用法在标准 SQL 里属于派生表Derived Table的场景很多主流数据库都支持但不同数据库的语法细节差异不小实际工作中踩过几次坑之后我把这套玩法彻底整理了一遍。这篇文章会从VALUES的本质讲起对比 SQL Server、MySQL、Oracle、PostgreSQL 的实现差异再结合几个实际场景说明怎么用它解决“临时映射”“批量造数”“打标分组”这些高频需求。最后把我在生产环境里踩过的坑一并列出来给正准备用的朋友做个参考。1. VALUES 到底是什么一句被人低估的 SQL 子句很多开发者对VALUES的印象停留在INSERT INTO ... VALUES里觉得它只是“往表里塞数据”的配套语法。实际上VALUES是 SQL 标准里的独立表达式它的完整能力是构造一张“行集”也就是一张完全存在于内存中的虚拟表。用它可以替代SELECT ... UNION ALL的繁琐写法也可以作为派生表直接参与JOIN、IN、EXISTS等操作。1.1 VALUES 的两种基本用法第一种就是大家最熟悉的INSERT语法这个不用多说。第二种才是本文的主角——把VALUES当作行集生成器直接用在FROM关键字后面。-- 核心写法把 VALUES 行集当作一张临时表 SELECT * FROM ( VALUES (1, 已完成), (2, 进行中), (3, 已取消) ) AS t(status_id, status_name);上面的写法在 SQL Server 和 PostgreSQL 里是通用的MySQL 的写法略有差异下文会专门讲。这条 SQL 执行后你得到的就是一张两列三行的虚拟表可以直接与真实表做JOIN也可以做聚合、排序、甚至直接作为数据源进行插入操作。1.2 为什么说它是“不用建表的临时表”临时表分两种一种是物理存在于tempdb中的#临时表需要显式创建、用完释放另一种就是这种“表值构造器”生成的虚拟行集它是 SQL 解析器在内存中维护的数据结构不落盘、不需要CREATE TABLE、也不需要DROP TABLE。用 VALUES 生成行集的核心价值在于不需要 DDL 权限很多生产环境只给SELECT权限建临时表建不了但VALUES派生表完全绕开了这个限制。不需要跨越会话维护#临时表在连接断开后自动消失但即便如此你也得先CREATE TABLE再INSERT两条语句起步而VALUES一条语句直接搞定。语义清晰把映射关系、配置数据直接写在业务 SQL 旁边阅读代码的人一眼就能看懂“状态 1 代表已完成”不用另开一个窗口去查字典表。需要特别说明的是VALUES生成的行集本质上仍然是一个“表达式”它没有索引、没有统计信息、也没有物理存储位置。它适合数据量小、生命周期短的场景不适合放大批量数据或高频查询的路径里。2. 用 VALUES 直接“凭空造表”三大主流数据库的实现对比虽然VALUES是标准 SQL 的语法但不同数据库在细节上各有脾气。这里挑最常见的四个数据库分别说一下先把写法差异看清楚后面才不会踩语法坑。2.1 SQL Server最典型的 VALUES 派生表写法SQL Server 对VALUES派生表的支持非常完整从 2008 版本开始就是成熟语法。写法如下SELECT mapping.status_id, mapping.status_name, orders.order_id, orders.amount FROM ( VALUES (1, 已完成), (2, 进行中), (3, 已取消) ) AS mapping(status_id, status_name) LEFT JOIN orders ON orders.status_id mapping.status_id;这里有两个关键点第一AS后面必须同时指定表别名和列别名。表别名不可省略列别名如果不写列名会变成无名的column1, column2之类虽然后续还能用但可读性大打折扣。第二SQL Server 对列名有两种写法兼容AS mapping(status_id, status_name)和AS mapping(status_id, status_name)是一样的但不要省略AS关键字。虽然某些版本不写也能跑但规范起见建议都写上。老版本 SQL Server2008 R2 及之前有些资料里会提到用SELECT 1 AS a UNION ALL SELECT 2的方式造临时数据实际上 SQL Server 2008 已经开始支持VALUES派生表了直接用VALUES写法可以少写一半的UNION ALL。2.2 MySQL老版本靠 UNION ALL新版本用 VALUES ROW()MySQL 的语法演进比较有意思。8.0.19 版本之前MySQL 不支持FROM (VALUES ...)这种标准写法常用的替代方案是SELECT ... UNION ALL。直到 8.0.19 引入了VALUES语句但它的语法又有自己的特点。MySQL 8.0.19 的写法SELECT * FROM ( VALUES ROW(1, 已完成), ROW(2, 进行中), ROW(3, 已取消) ) AS mapping(status_id, status_name);注意MySQL 的VALUES子句必须用ROW(...)包裹每个行值这点和 SQL Server / PostgreSQL 不同。如果漏写ROW()MySQL 会直接报语法错误。8.0.19 之前的老版本包括 5.7 和更早的版本只能这么写SELECT 1 AS status_id, 已完成 AS status_name UNION ALL SELECT 2, 进行中 UNION ALL SELECT 3, 已取消;这个写法虽然也能达到目的但随着行数增加SQL 文本会变得特别冗余。如果你维护的是老项目建议在代码注释里标清楚是 MySQL 版本限制导致的UNION ALL写法避免后人看到以为你偏爱 UNION ALL。另外MySQL 8.0.31 版本引入了VALUES TABLE语法可以将多行数据当作临时表直接使用但这里不展开因为VALUES ROW()的写法已经能满足绝大多数场景。2.3 Oracle从 DUAL 到 VALUES 的演进差异Oracle 的情况最特殊。在 23c 版本之前Oracle 的标准SELECT语法里不支持FROM (VALUES ...)这种表值构造器写法传统的做法是SELECT ... FROM DUAL加UNION ALLSELECT 1 AS status_id, 已完成 AS status_name FROM DUAL UNION ALL SELECT 2, 进行中 FROM DUAL UNION ALL SELECT 3, 已取消 FROM DUAL;Oracle 23c 引入了VALUES子句语法和 SQL Server 类似SELECT * FROM ( VALUES (1, 已完成), (2, 进行中), (3, 已取消) ) AS mapping(status_id, status_name);如果你还在用 Oracle 12c/19c老老实实用UNION ALL方案最稳妥。另外 Oracle 还有一种SYS.ODCIVARCHAR2LIST之类的方法用于构造单列数据集合配合TABLE()函数使用但通常只适合做单列列表灵活性不如多列行集。2.4 PostgreSQL标准用法以及和 GENERATE_SERIES 的区别PostgreSQL 的表值构造器支持非常标准写法和 SQL Server 完全一致SELECT * FROM ( VALUES (1, 已完成), (2, 进行中), (3, 已取消) ) AS mapping(status_id, status_name);PostgreSQL 还支持VALUES配合ORDER BY和LIMIT这在其他数据库里不一定兼容SELECT * FROM ( VALUES (1, 已完成), (2, 进行中), (3, 已取消) ) AS mapping(status_id, status_name) ORDER BY status_id DESC LIMIT 2;这里有个容易混淆的点GENERATE_SERIES(1, 10)也能生成一组连续数字但它生成的是“单列数据集”而VALUES是“多列行集”两者用途不一样。GENERATE_SERIES适合做日期序列、数字序列VALUES适合做离散的映射数据。我把四个数据库的差异整理成一个表方便对照查阅数据库版本要求VALUES 派生表语法备注SQL Server2008VALUES (1,a), (2,b)后跟AS t(col1, col2)必须加表别名和列别名PostgreSQL全部支持VALUES (1,a), (2,b)后跟AS t(col1, col2)支持 ORDER BY / LIMITMySQL8.0.19VALUES ROW(1,a), ROW(2,b)后跟AS t(col1, col2)必须用 ROW() 包裹Oracle23cVALUES (1,a), (2,b)后跟AS t(col1, col2)旧版本只能用 DUAL UNION ALL3. VALUES 临时表的高频实战场景知道语法只是第一步真正让VALUES发挥价值的是它在实际需求里的灵活运用。这里列几个我在项目里真正用过的场景都是从业务需求中抽象出来的。3.1 跑批脚本里的“配置映射表”省一次建表做数据清洗或者 ETL 的时候最烦的一种情况是业务给了一个“翻译规则”比如把渠道编码A001翻译成线上App把A002翻译成线下门店但这种规则只在本批次生效不值得为它建一张永久表。我通常直接写成这样SELECT source.order_id, source.channel_code, channel_map.channel_name FROM source_orders AS source INNER JOIN ( VALUES (A001, 线上App), (A002, 线下门店), (A003, 电话销售) ) AS channel_map(channel_code, channel_name) ON source.channel_code channel_map.channel_code;这样整条 SQL 自带映射关系不依赖额外表排错的时候只需要看一段代码。数据量只要在几百行以内性能完全没问题。3.2 结合 INSERT 批量插入数据还有一个常见的场景是往临时表或正式表里灌一批测试数据。传统做法是写多条INSERT或者先拼SELECT ... UNION ALL再INSERT INTO ... SELECT。有了VALUES派生表一行语句就能完成INSERT INTO dim_calendar (day_type, day_desc) SELECT day_type, day_desc FROM ( VALUES (1, 工作日), (2, 周末), (3, 节假日) ) AS t(day_type, day_desc);这条 SQL 在 SQL Server、PostgreSQL、MySQL 8.0.19 里都可以跑Oracle 23c 也可以。相比逐条插入这种写法把数据集中在一处容易审查也方便后续维护。实际工作中我还遇到过一种情况需要把 Excel 里的数据快速导入到数据库做临时比对。此时可以把 Excel 的数据拼成VALUES行集直接与数据库表做EXCEPT或INTERSECT比较找出差异数据比导入临时表再比对少了好几个步骤。3.3 在 CTE 里初始化数据源VALUES可以和 CTECommon Table Expression完美组合。特别是做复杂数据转换时可以在 CTE 开头先定义一组“基准数据”然后在后续步骤里逐步加工WITH score_levels(level_code, min_score, max_score) AS ( SELECT * FROM ( VALUES (S, 90, 100), (A, 80, 89), (B, 70, 79), (C, 60, 69), (D, 0, 59) ) AS t(level_code, min_score, max_score) ) SELECT student_id, score, score_levels.level_code FROM student_scores INNER JOIN score_levels ON student_scores.score BETWEEN score_levels.min_score AND score_levels.max_score;CTE 的好处是可以把VALUES放在语句的最前面整个查询结构非常清晰。后续如果再需要对这个“临时的配置表”做多次引用也只需要引用 CTE 名称即可。3.4 用 VALUES 等价替代 UNION ALL如果要把多个分类的汇总结果放在同一列里展示最常见的做法是UNION ALL。实际上VALUES在多行静态数据拼接的场景里往往比UNION ALL更简洁。以一个场景为例统计订单表里广东、北京、上海三个区域的订单量但区域编码分散在不同字段里需要手动指定一批区域信息SELECT region_name, COUNT(*) AS order_cnt FROM orders INNER JOIN ( VALUES (GD, 广东), (BJ, 北京), (SH, 上海) ) AS region_map(region_code, region_name) ON orders.region_code region_map.region_code GROUP BY region_name;这段 SQL 在逻辑上和UNION ALL写法完全等价但代码量减少了一半。更重要的是当区域列表发生变化时只需要改VALUES里的行值不需要修改SELECT结构维护成本更低。3.5 数据分析时给明细数据“打标”数据分析场景里经常需要根据离散值给数据分组。比如给会员打上“高价值客户”“普通客户”“沉睡客户”的标签但标准不是简单的数值区间而是一个个具体的会员 ID。这个场景用VALUES特别合适WITH vip_list(member_id) AS ( SELECT * FROM ( VALUES (M10001), (M10002), (M10003), (M10004) ) AS t(member_id) ) SELECT m.member_id, m.member_name, CASE WHEN v.member_id IS NOT NULL THEN 高价值 ELSE 普通 END AS member_level FROM members AS m LEFT JOIN vip_list AS v ON m.member_id v.member_id;这种写法的好处是如果不单独建表这个“高价值客户名单”就直接显示在 SQL 里。业务方如果想临时换一批名单直接改VALUES里的字符串就行不需要 DBA 介入非常适合分析人员自助使用。4. 实际踩坑记录与细节提醒任何语法用熟了才会发现问题VALUES派生表也不是哪儿都好用。下面这些坑大部分是我自己在项目里实际踩过的。4.1 列数必须严格一致包括注释行VALUES的每一行都必须有相同数量的列。比如你写了(1, 已完成)那么后面每一行都必须是两列。这个看起来很好理解但真正出问题的是在调整数据时——删掉某一行的某个字段后容易漏改其他行。错误示例SELECT * FROM ( VALUES (1, 已完成), (2, 进行中), -- 这里少了一列 (3, 已取消, 备注) -- 这里多了一列 ) AS t(status_id, status_name);数据库会直接报列数不一致的错误。这类错误本身不可怕但排查起来会浪费时间因为错误信息通常不会提示你“第 2 行少了一列”。建议写完VALUES后立刻执行一次确认无误再往上加逻辑。4.2 隐式类型转换的坑VALUES里每一列的数据类型是根据第一行推导的。如果第一行写了整数1但第二行写了字符串abc数据库会做隐式转换尝试。一旦转换失败直接报错。更隐蔽的问题是当后续用法里涉及到类型比较、排序时推导出的类型可能与你的预期不一致。举个例子SELECT * FROM ( VALUES (001, 第一), (002, 第二) ) AS t(code, name) WHERE code 1;这个例子里code列推导出的是字符串类型但你用code 1去过滤有些数据库会做隐式转换有些数据库尤其 MySQL 的某些模式会返回空结果或报错。建议在VALUES中保持类型统一不要让数据库做隐式转换。4.3 AS t(col1, col2) 的别名顺序不可乱AS mapping(status_id, status_name)中行值位置与列别名是一一对应的。第 1 个值对应status_id第 2 个值对应status_name。如果你把别名顺序写反了后续引用就是错位的——这种问题特别隐蔽SQL 不报错但逻辑完全是反的。个人习惯是在写VALUES行集前先写好列别名注释比如-- 列顺序status_id, status_name, sort_order SELECT * FROM ( VALUES (1, 已完成, 1), (2, 进行中, 2), (3, 已取消, 3) ) AS t(status_id, status_name, sort_order) ORDER BY sort_order;4.4 ORDER BY 放在派生表里还是外面在使用VALUES构造数据时如果涉及排序需要特别注意 SQL 的逻辑顺序。ORDER BY应该放在最外层的查询里而不是放在派生表内部虽然 PostgreSQL 支持在子查询里用ORDER BY但很多数据库不支持而且这在逻辑上没有意义因为行集本身没有顺序概念。正确用法SELECT * FROM ( VALUES (1, c), (2, a), (3, b) ) AS t(id, val) ORDER BY val ASC;4.5 大量数据时的性能思考VALUES行集的数据量如果过大性能会有明显下降。通常建议行数控制在 500 行以内超过这个量最好生成临时表或使用 CTE 物化。因为VALUES是内存中的行集数据库优化器对它做统计和索引选择的能力很弱当它与大型事实表JOIN时可能产生较差的执行计划。我之前处理过一个案例同事用VALUES构造了 3000 多行数据与一个千万级订单表做INNER JOIN整个查询跑了快两分钟。后来把这 3000 行数据导入临时表并给关联字段加了索引查询时间降到了 5 秒以内。VALUES适合解决“小配置数据集”的问题不适合做大中型数据集的载体。4.6 Oracle 旧版本的替代方案受限Oracle 23c 以下只能用UNION ALL方案行数一多SQL 会变得非常长。此时有两个替代思路第一是使用SYS.ODCIVARCHAR2LIST配合TABLE()函数做单列列表第二是使用多行INSERT ALL语法构造测试数据。但说实话最好的办法还是建一张临时表。5. 这些写法如何延伸到窗口函数与更复杂的查询VALUES不只能做简单的映射它和窗口函数配合时也有很多巧妙用法。尤其是当需要“手工指定排序基准”时VALUES能解决别的语法很难处理的问题。5.1 通过 VALUES 指定自定义排序规则数据库默认排序只支持升序/降序如果想让数据按“业务自定义顺序”排列比如按颜色红、黄、蓝、绿可以直接 JOIN 一个VALUES行集按序号排序SELECT product_name, color FROM products INNER JOIN ( VALUES (红, 1), (黄, 2), (蓝, 3), (绿, 4) ) AS color_order(color, sort_no) ON products.color color_order.color ORDER BY color_order.sort_no;这个技巧在做报表固定排列顺序的时候非常有用比FIELD()、DECODE()的方案更清晰也跨数据库通用。5.2 配合窗口函数做手工分组窗口函数里有一个场景需要手动为若干人员分配“固定的分组次序”。比如把一批销售按“A/B/C”三个组交替分配可以用VALUES生成组编号然后与通号做MOD映射WITH sales_order AS ( SELECT sales_name, ROW_NUMBER() OVER (ORDER BY sales_name) AS rn FROM sales_team ), group_config AS ( SELECT * FROM ( VALUES (1, A组), (2, B组), (3, C组) ) AS t(rn, group_name) ) SELECT sales_name, group_name FROM sales_order INNER JOIN group_config ON group_config.rn ((sales_order.rn - 1) % 3) 1;巧用VALUES生成“人工可控”的关联键能省掉一大串无语义的CASE WHEN也让 SQL 的意图清晰可读。5.3 多行 VALUES 配合 EXISTS 替代 IN 列表当IN后面的列表特别长时有些数据库对IN (1000个值)的优化并不理想。可以把这批值放在VALUES行集里再用EXISTS关联性能往往更稳定SELECT * FROM orders WHERE EXISTS ( SELECT 1 FROM ( VALUES (P10001), (P10002), (P10003) ) AS target(product_id) WHERE target.product_id orders.product_id );这个写法在做“大批量 ID 过滤”时特别实用也比直接拼IN (P10001,P10002,...)更清晰尤其当过滤列表来自业务方给的 Excel 文件时直接拼成VALUES格式最方便。5.4 配合 EXCEPT / INTERSECT 做数据比对如果有一组期望数据要跟数据库里的实际数据做差异比对VALUES派上用场-- 期望状态定义 WITH expected_status(status_code, status_name) AS ( SELECT * FROM ( VALUES (1, Pending), (2, In Progress), (3, Completed) ) AS t(status_code, status_name) ) -- 找出数据库中缺失的状态 SELECT * FROM expected_status EXCEPT SELECT status_code, status_name FROM dim_status;这种写法在做数据迁移验证、字典表完整性校验时非常高效不依赖任何中间表。6. 排查思路与验证方法VALUES派生表的问题虽然不多但一旦出错很多人会以为是JOIN或查询整体的问题找不到根源。我通常按照以下顺序排查6.1 第一步单独执行 VALUES 部分不要带着整个查询去排查。先把FROM (VALUES ...) AS t(...)这段拎出来单独执行SELECT * FROM ( VALUES (1, 已完成), (2, 进行中), (3, 已取消) ) AS t(status_id, status_name);如果这段能跑通说明行集本身没问题问题出在后面的JOIN、WHERE、GROUP BY等逻辑上。如果这段报错就要检查列数是否一致、类型是否兼容、别名是否完整。6.2 第二步确认列别名与引用名称一致列别名写错或漏写是最常见的低级错误。确认方式很简单先跑一遍SELECT *看看返回的列名是什么再检查后续逻辑引用的列名是否一致。6.3 第三步关注类型推导在跨类型比较时比如把VALUES里的字符串列与整数列做比较最好查询一下该列推导出的数据类型。SQL Server 可以用sp_describe_first_result_setPostgreSQL 可以直接查information_schema.columns。多花十秒钟确认类型能避免后面出现隐式转换的坑。6.4 第四步验证执行计划如果VALUES参与了复杂查询且性能不佳用EXPLAIN看执行计划重点关注以下三点VALUES行集是否被物化是否生成了不合理的嵌套循环连接是否存在隐式类型转换导致索引失效之前排查过一个慢 SQL问题就在于 MySQL 老版本用UNION ALL构造几千行数据再 JOIN优化器生成了一个巨大的中间结果后来改为 CTE 物化才好一些。根据个人经验VALUES最适合的是“小数据量、高灵活性、低改动成本”的场景。如果你的临时数据量超过几百行或者需要被多个查询重复引用建议还是老老实实建临时表或者用 CTE 物化没必要为了“不建表”而牺牲查询性能。还有一个小技巧分享给大家在 IDEA 数据库插件或 DBeaver 里测试 SQL 时经常需要随手造几条数据验证逻辑此时VALUES派生表是效率最高的方案不需要建表也不需要导数据一段 SQL 就能模拟真实场景写完就能验证窗口函数、JSON 聚合、行转列这些逻辑。用习惯了之后你会发现它成了日常调试里最高频的利器之一。