1. 从一个调试场景说起为什么需要“临时造数据”做数据库开发的人几乎都遇到过这种尴尬线上有个报表逻辑要验证但测试环境里没有合适的数据或者要排查一条 SQL 的关联逻辑对不对但手头只有表结构没有现成记录。以前我碰到这种情况第一反应是INSERT INTO塞几条记录进临时表用完再删。这个办法能用但很啰嗦——先建表、再插数、用完还得清理一套流程走下来光写样板代码就够烦的。后来我开始大量使用VALUES来直接构造临时数据集这才发现一个很舒服的事实大部分“临时造数据”的需求根本不需要建表用VALUES一句 SQL 就能搞定。它可以作为独立的查询语句直接跑可以嵌到WITH公共表表达式里当作临时表用也可以配合INSERT INTO一次性批量灌入真实表。这里的核心思想是把VALUES当成一个匿名的内存表数据即查即用用完即走不落盘、不建对象、不留垃圾。适合看这篇文章的人我大致分三类日常要做报表开发、SQL 调试的数据分析师或后端开发想在测试时快速造数据需要写自动化脚本、做单元测试的测试工程师想用少量代码构造各种边界数据以及那些刚接触 SQL、想知道“不建表还能怎么造数据”的新手学习者。这篇文章我会把这些年用VALUES构造临时表的经验完整梳理一遍包括标准语法、各数据库的兼容性细节、和临时表/CTE 的组合玩法以及我实际踩过的一些坑。内容偏实操看完可以直接复制去跑。2. 先搞懂 VALUES 的本质别把它当成“只属于 INSERT 的边角料”2.1 从标准 SQL 角度理解 VALUES 到底是什么很多开发者的第一反应是VALUES不就是INSERT INTO后面跟的那一段吗这个理解没有错但太局限了。在 SQL 标准里VALUES本身就是一个完整的“表构造函数”table value constructor它可以独立返回一张表。拿最基础的例子来说SELECT * FROM (VALUES (1, Alice), (2, Bob)) AS t(id, name);这段 SQL 在 SQL Server、PostgreSQL、Oracle需加FROM dual或特定写法、MySQL 8.0 里都能返回一张两行两列的表idname1Alice2Bob这里的核心点是VALUES的每一对括号就是一行记录括号内的每个值就是一个字段。整个VALUES子句本身具备“表”的语义可以出现在FROM子句里能出现的绝大多数位置。理解这一点之后你就会发现很多以前要先建临时表的场景其实都能用一行VALUES替代。为了让大家更直观地理解我用一个生活化类比VALUES就像你在纸上直接手写一张购物清单表结构就是清单的列商品名、数量、价格每一对括号就是一行条目。你用完之后把纸一揉不需要专门准备一个“购物清单文件夹”。2.2 为什么说 VALUES 构造的是“临时表”严格来说VALUES构造出来的数据集在数据库内部是一个派生表derived table或者内联视图。它不像物理临时表那样占用 tempdbSQL Server或者临时表空间也不涉及日志记录数据库执行完就释放。所以它天然具有“临时”的特性。我之前在 SQL Server 里做过一次粗略对比同样的数据量10万行级别用#temp物理临时表插入再查询和用VALUES构建派生表直接关联后者的额外开销小很多尤其在纯内存计算时两者的性能差距可以到几倍甚至十几倍。当然如果你的数据量达到百万行以上千万级别我不建议继续用VALUES硬扛那个场景下物理临时表或者表变量才是合理的。VALUES的最佳适用区间是“小数据量、临时计算、快速验证”这个边界要心里有数。2.3 一个容易被忽略的底层逻辑行构造器的类型推断用VALUES的时候数据库会自动推断每一列的数据类型。这里的规则是同一列的所有行的字面量类型会做“兼容合并”合并的结果就是这列最终的类型。举例来说SELECT * FROM (VALUES (1, 10), (2, 20)) AS t(id, val);第一行第二列是字符串10第二行第二列是数字20。在 SQL Server 里系统会把这列推断成int并尝试把10隐式转换成数字在 PostgreSQL 里也可能做类似处理。但如果某个值没法转换比如abc和20混在一起查询就会报错。这一点我在第 6 部分会有更多实战细节。所以我的建议是用VALUES造数据时尽量保持同一列的类型一致。哪怕需要不同类型也请统一用显式类型转换避免数据库自己猜。3. 临时数据集的核心玩法CTE、派生表和 INSERT INTO 三件套3.1 用 VALUES CTE 构建“逻辑临时表”告别反复建表大多数场景下我喜欢把VALUES和公共表表达式CTE配合使用。CTE 本身就能像临时表一样在一条 SQL 的范围内反复被引用而VALUES负责给 CTE 提供数据两者结合起来会非常顺手。WITH temp_data (order_id, customer_name, amount) AS ( SELECT * FROM (VALUES (1001, 张三, 58.50), (1002, 李四, 120.00), (1003, 王五, 35.80) ) AS td(order_id, customer_name, amount) ) SELECT customer_name, SUM(amount) AS total_amount FROM temp_data GROUP BY customer_name;这里我建了一个叫temp_data的逻辑临时表它只在当前这条 SQL 语句执行期间存在。后续你可以继续 JOIN 其他真实表也可以在 CTE 里做多次计算。整个过程没有创建任何物理对象也不需要显式DROP非常适合做数据清洗、数据校验、报表口径验证这些场景。有一个细节值得注意在 SQL Server 里WITH temp_data (order_id, ...)这种在 CTE 名称后直接带列名列表的写法是支持的PostgreSQL 也支持。但 MySQL 8.0 里用 CTE 时列的别名应该写在 CTE 内部WITH temp_data AS ( SELECT * FROM (VALUES ROW(1001, 张三, 58.50), ROW(1002, 李四, 120.00) ) AS td(order_id, customer_name, amount) ) SELECT * FROM temp_data;两种差异不大写的时候注意一下兼容性就好。3.2 直接在 FROM 子句里用 VALUES 做“内联临时表”如果只是临时关联一个小集合不想写 CTE也可以直接把VALUES放在FROM后面当派生表用。这一招特别适合处理“代码集”类的需求比如根据一批指定 ID 过滤数据要保留原始顺序。SELECT t.id, t.sort_order FROM (VALUES (101, 3), (102, 1), (103, 2) ) AS t(id, sort_order) INNER JOIN products p ON p.product_id t.id ORDER BY t.sort_order;这段 SQL 就实现了一个常见需求按指定的 ID 顺序返回产品列表。原来你得写一堆CASE WHEN或者用CHARINDEX去处理自定义排序现在用VALUES把“ID-序号”对应表造出来再 JOIN代码可读性一下子高很多。我经常在接口的 ID 列表排序、批量状态更新这类场景里用它。3.3 用 VALUES 给已存在的临时表批量灌数据还有一种场景你已经有了物理临时表#temp或TEMPORARY TABLE需要用VALUES往里快速插入多行。SQL Server 从 2008 开始支持多行 VALUES 插入MySQL、PostgreSQL 更是默认支持。CREATE TABLE #temp_products ( product_code VARCHAR(20), price DECIMAL(10,2) ); INSERT INTO #temp_products (product_code, price) VALUES (SKU-A001, 19.99), (SKU-A002, 29.99), (SKU-B001, 9.99);这个操作之前我最常犯的错就是忘记在VALUES前加列名列表就直接INSERT INTO #temp_products VALUES...。虽然这样语法上也能过但一旦表的列顺序改动过数据就串位了。所以老规矩显式列名一定要写。这也是一个习惯问题养成好习惯能少踩很多坑。4. 格式不同别掉坑主流数据库 VALUES 语法差异速查很多人觉得自己会写 SQL Server 的VALUES到了 PostgreSQL 或者 MySQL 就懵了。原因是不同数据库对“行构造器”的语法支持并不完全一样。以下是我根据自己多数据库开发经验整理的差异点。4.1 SQL Server简单直接还可以用 VALUES 做 UPDATE 数据源SQL Server 里最标准的写法就是每一行括号加逗号隔开SELECT * FROM (VALUES (1, A), (2, B)) AS t(id, val);不需要在每行前写ROW关键字也没那么多装饰。SQL Server 还支持VALUES用在MERGE语句的USING子句里比如你要根据一个临时数据集去匹配更新目标表MERGE INTO products AS target USING (VALUES (101, 新版产品A, 25.00), (102, 新版产品B, 35.00) ) AS source(product_id, product_name, price) ON target.product_id source.product_id WHEN MATCHED THEN UPDATE SET product_name source.product_name, price source.price;这个技巧特别适合批量刷数据、做一次性数据订正。比起一条一条写UPDATE用VALUES构造数据集再MERGE效率和正确率都高不少。4.2 PostgreSQL需要 ROW 关键字且 VALUES 自带类型转换能力PostgreSQL 的写法稍微严一点推荐用ROW关键字显式构造行SELECT * FROM (VALUES ROW(1, Alice), ROW(2, Bob) ) AS t(id, name);不带ROW也支持但带上更清晰。PostgreSQL 的VALUES列表有一个特性如果同一列出现不同类型它会用UNION的类型推断规则去寻找“公共类型”。比如VALUES (1, 2024-01-01), (2, 2024-01-02)第二列会被自动识别为date类型这一点比 SQL Server 要聪明一些。4.3 MySQL8.0 才完整支持 VALUES 表构造器老版本要绕路MySQL 在 8.0 里终于支持了独立的VALUES构造表SELECT * FROM (VALUES ROW(1, Alice), ROW(2, Bob) ) AS t(id, name);但如果你还在维护 5.7 及更老的库用不了这种写法。老版本的替代方案是用多个SELECT ... UNION ALL来模拟虽然丑一点但效果一样SELECT 1 AS id, Alice AS name UNION ALL SELECT 2, Bob;4.4 Oracle从 23c 开始支持老版本用 FROM dual 拼接Oracle 对VALUES表构造器的支持比 MySQL 还晚23c 才开始原生支持较好。老版本里最常用的是SELECT ... FROM dual加UNION ALLSELECT 1 AS id, Alice AS name FROM dual UNION ALL SELECT 2, Bob FROM dual;Oracle 也有一种SYS.ODCIVARCHAR2LIST或SYS.ODCINUMBERLIST构造集合的写法配合TABLE()函数也能当表用但可读性和通用性都比较差。如果你在 Oracle 环境里非要用VALUES风格建议升级到 23c或者在旧版本老老实实用UNION ALL。我把主要差异整理成了下面的速查表方便大家查阅数据库VALUES 独立使用多行 INSERT推荐写法SQL Server支持无需 ROW支持(VALUES (1,A),(2,B)) AS t(id,val)PostgreSQL支持推荐 ROW支持(VALUES ROW(1,A),ROW(2,B)) AS t(id,val)MySQL 8.0支持必须 ROW支持(VALUES ROW(1,A),ROW(2,B)) AS t(id,val)MySQL 5.7不支持支持用SELECT ... UNION ALL代替Oracle 23c支持支持类似标准 SQLOracle 旧版不支持老版本仅支持单行用FROM dualUNION ALL代替5. VALUES 的进阶应用场景我用它解决过的几个实际问题5.1 用 VALUES 生成连续数字序列替代递归 CTE 的笨办法有时候我们想要一张“1 到 1000 的连续数字表”用于统计连续日期、补齐缺失行或做排列组合。很多人的第一反应是写递归 CTE但用VALUES加上交叉连接CROSS JOIN也可以非常高效地生成WITH nums AS ( SELECT * FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS d(n) ) SELECT a.n b.n * 10 c.n * 100 AS num FROM nums a CROSS JOIN nums b CROSS JOIN nums c ORDER BY num;这段代码生成了 0 到 999 的 1000 个数字利用了三组数字的笛卡尔积。整个过程没有递归、没有循环执行效率很高。有了这张数字表你可以去补日期序列、做分页模拟、按数字拆字符串等等。我自己做日历报表的时候经常用这个思路先构造一个完整的日期序列再左连接业务表这样就能保证报表上每一天都有记录不会出现日期断层。5.2 在存储过程里用 VALUES 构造参数集合避免拼装 SQL有些存储过程需要接收一个“ID 数组”但存储过程的参数不好直接传列表。以前很多人选择把 ID 列表拼成一个逗号分隔的字符串然后在存储过程里用字符串拆分函数处理不仅慢而且容易出错。后来我习惯让调用方直接把数据以VALUES的形式放到 SQL 里当成派生表来传参CREATE PROCEDURE usp_GetProductInfo AS BEGIN SELECT ... FROM products p INNER JOIN (VALUES (1001, 5), (1002, 3), (1003, 8) ) AS param(product_id, quantity) ON p.product_id param.product_id; END;这里的param表就充当了“输入参数集合”的角色。如果你的 ORM 框架支持构造这种 SQL那么很多批量操作都会变得非常简单。5.3 配合字符串拆分函数生成“动态 IN 列表”有时候我们传入的是1,5,9,20这种字符串直接 IN 肯定不行。结合VALUES和递归或拆分函数可以做出一套比较优雅的拆解方案这里以 SQL Server 的STRING_SPLIT为例DECLARE ids VARCHAR(100) 1,5,9,20; SELECT value AS id FROM STRING_SPLIT(ids, ,) WHERE TRY_CAST(value AS INT) IS NOT NULL;如果你用的是更老的版本没有STRING_SPLIT也可以用VALUES构造一个“十位数表”来配合SUBSTRING拆分。思路本质一样先有一张数字序列表再对字符串按位置切片。我在做一个老的 2008R2 项目时就是靠这个思路解决了“字符串 ID 列表如何关联查询”的问题。5.4 数据清洗时用 VALUES 建映射表一步完成翻译转换做数据清洗时经常遇到编码值和显示值不一致的情况比如数据库里存的是0/1要显示成男/女或者状态字段存的N/P/F要翻译成中文。常规做法是左连接一张字典表但字典表要维护。如果只是临时做一次清洗完全可以用VALUES现场建映射SELECT raw.user_code, raw.status_code, mapping.status_name FROM user_raw_data raw LEFT JOIN (VALUES (N, 新建), (P, 进行中), (F, 已完成) ) AS mapping(status_code, status_name) ON raw.status_code mapping.status_code;这招在处理一次性数据迁移、临时报表、接口联调时特别香。不用事先建字典表也不用提交数据库变更脚本一条 SQL 自己就闭环了。6. 我实际踩过的 VALUES 相关的几个坑6.1 列数不一致直接报错最常见的问题是行的列数不一致。比如SELECT * FROM (VALUES (1, A), (2) ) AS t(id, val);第二行只有一列数据库会直接报“列数不匹配”的错误。这个问题在新手身上出现得特别多而且有时候肉眼扫很难发现。我的经验是写完 VALUES 列表之后数一下每行的括号内有几个值尤其是复制粘贴改数据的时候特别容易删多或删少。我自己有几次就是从 10 行数据里删掉中间一行结果把一列顺手删了查了半天才反应过来。6.2 列类型冲突与隐式转换陷阱前面提到过类型推断的问题。再举一个我实际遇到的例子有次我把一个参数NULL放进了VALUES结果 SQL Server 推断这一列为int后来想 CAST 成varchar时报错。正确做法是在NULL旁边用显式类型转换SELECT * FROM (VALUES (1, CAST(NULL AS VARCHAR(20))), (2, Alice) ) AS t(id, name);把NULL单独放在VALUES里时数据库不知道它是字符串、数字还是日期所以最好用CAST或CONVERT明确指定类型。这一点在“向临时表插入数据后继续 JOIN”时尤其重要不然等到运行到后面才发现类型对不上排查成本就高了。6.3 关键字冲突和别名缺失很多数据库要求派生表必须有别名。我在 PostgreSQL 里曾经写过SELECT * FROM (VALUES (1, 2));直接报错必须给派生表加别名SELECT * FROM (VALUES (1, 2)) AS t(a, b);另外如果列名用了数据库的保留字比如order、group、key记得用反引号MySQL或双引号标准 SQL包裹。SQL Server 用方括号[order]PostgreSQL 用双引号order。这个坑我在 MySQL 里踩过好几次因为key太常见了不注意就会变成语法错误。6.4 太长的 VALUES 列表会导致 SQL 难以维护尽管VALUES很好用但你别把它撑成一个几百行的巨型列表。我见过有人在代码里写了一个 2000 行数据的 VALUES 集合结果阅读、调试、排错都极其痛苦。遇到这种情况建议采用临时表 批量插入或者从 Excel 生成 INSERT 脚本。VALUES适合几十行以内的小数据集合一旦超过这个量级我就开始考虑物理临时表了。这个度每个人可以结合自己的习惯但别让一段 SQL 膨胀成了“数据文件”。6.5 不同数据库对 NULL 排序的差异影响如果VALUES造出来的临时表里有 NULL在后面做排序或分组的时候不同数据库的行为不一样。SQL Server 中 NULL 默认排在前面PostgreSQL 默认排在最后MySQL 中 NULL 也排在前面。这有时会打乱你的预期结果。如果是做报表最好明确用ORDER BY ... NULLS LAST或COALESCE把 NULL 转成默认值避免环境不同导致结果不同。7. 常见问题与排查技巧实录我在技术群里经常看到有人问 VALUES 相关的问题挑几个典型的把排查思路也列出来做成一个速查表方便大家以后直接照着查。问题现象可能原因解决思路The column prefix does not match with a table name派生表没加别名在 VALUES 闭合括号后加上AS t(列1, 列2)VALUES list has different number of columns某行列数比其他行少或多逐行数括号内字段数量保证一致Error converting data type varchar to int同一列混了数字和字符串统一类型必要时用CAST/TRY_CASTIncorrect syntax near ,多了或少了括号检查逗号和括号的配对推荐编辑器高亮功能Null value is eliminated by aggregate or other SET operation聚合时遇到 NULL用ISNULL/COALESCE先处理默认值结果顺序不符合预期VALUES表没有自带顺序语义额外加一列序号按序号ORDER BYMySQL 5.7 里语法直接报错老版本不支持 VALUES 表构造器改成SELECT ... UNION ALL这里再单独说一个很隐蔽的坑在 SQL Server 里用过大的数字直接放入VALUES可能会被推断为numeric或decimal导致精度变化。比如(100000000000000000000)这种超长数字直接放进 VALUES再和其他bigint列做 JOIN 时类型不匹配会引发隐式转换可能导致索引失效。解决办法还是老办法显式CAST成目标类型。另外如果发现VALUES构造的表在查询计划里出现了“Table Scan”或“Constant Scan”这是正常的数据量小时无需担心。如果数据量大导致查询变慢那是VALUES用错了场景要果断换成临时表。8. 实践中的选择判断什么时候用 VALUES什么时候老实建临时表用VALUES构造临时数据有很多优势但它不是万能的。我给自己定了几条原则供大家参考其一数据量在几百行以内的、临时验证性质的优先用VALUES。不需要 DDL不需要考虑事务和日志SQL 跑完就完事。这也是它最舒服的场景。其二数据量在上千到几万行、而且后续会多次引用老老实实建临时表。物理临时表有统计信息可以被索引可以被多个批次访问性能和稳定性都更好。其三数据从别的表查出来而不是手写的那直接用SELECT ... INTO或者CREATE TABLE AS别把数据复制粘贴成VALUES那样又慢又容易出错。其四需要跨多条 SQL 语句共享数据集时用临时表或表变量别指望VALUES能跨语句存活。VALUES的“临时性”仅限于它所在的那一条 SQL 语句内部。这套判断逻辑说白了就是看数据量和生命周期。生命周期短、量小就VALUES生命周期长、量大就临时表。没有绝对的哪个更好只有合不合适。最后分享一个我自己的使用习惯我经常用 VALUES 构建一个“期望结果表”然后把真实查询结果跟它对比来做快速数据校验。比如算一个汇总口径我先把用手工算好的答案写成 VALUES 表再EXCEPT一下真实结果两者不一致立刻能看出来。这个技巧帮我节省了大量核对时间也算是对VALUES的一个独创用法吧。希望这些内容对你也有实实在在的帮助。 
长河君 · 2026/9/17 21:48:01 · 阅读 1.2k SQL中VALUES构造临时表的实用技巧与避坑指南
1. 从一个调试场景说起为什么需要“临时造数据”做数据库开发的人几乎都遇到过这种尴尬线上有个报表逻辑要验证但测试环境里没有合适的数据或者要排查一条 SQL 的关联逻辑对不对但手头只有表结构没有现成记录。以前我碰到这种情况第一反应是INSERT INTO塞几条记录进临时表用完再删。这个办法能用但很啰嗦——先建表、再插数、用完还得清理一套流程走下来光写样板代码就够烦的。后来我开始大量使用VALUES来直接构造临时数据集这才发现一个很舒服的事实大部分“临时造数据”的需求根本不需要建表用VALUES一句 SQL 就能搞定。它可以作为独立的查询语句直接跑可以嵌到WITH公共表表达式里当作临时表用也可以配合INSERT INTO一次性批量灌入真实表。这里的核心思想是把VALUES当成一个匿名的内存表数据即查即用用完即走不落盘、不建对象、不留垃圾。适合看这篇文章的人我大致分三类日常要做报表开发、SQL 调试的数据分析师或后端开发想在测试时快速造数据需要写自动化脚本、做单元测试的测试工程师想用少量代码构造各种边界数据以及那些刚接触 SQL、想知道“不建表还能怎么造数据”的新手学习者。这篇文章我会把这些年用VALUES构造临时表的经验完整梳理一遍包括标准语法、各数据库的兼容性细节、和临时表/CTE 的组合玩法以及我实际踩过的一些坑。内容偏实操看完可以直接复制去跑。2. 先搞懂 VALUES 的本质别把它当成“只属于 INSERT 的边角料”2.1 从标准 SQL 角度理解 VALUES 到底是什么很多开发者的第一反应是VALUES不就是INSERT INTO后面跟的那一段吗这个理解没有错但太局限了。在 SQL 标准里VALUES本身就是一个完整的“表构造函数”table value constructor它可以独立返回一张表。拿最基础的例子来说SELECT * FROM (VALUES (1, Alice), (2, Bob)) AS t(id, name);这段 SQL 在 SQL Server、PostgreSQL、Oracle需加FROM dual或特定写法、MySQL 8.0 里都能返回一张两行两列的表idname1Alice2Bob这里的核心点是VALUES的每一对括号就是一行记录括号内的每个值就是一个字段。整个VALUES子句本身具备“表”的语义可以出现在FROM子句里能出现的绝大多数位置。理解这一点之后你就会发现很多以前要先建临时表的场景其实都能用一行VALUES替代。为了让大家更直观地理解我用一个生活化类比VALUES就像你在纸上直接手写一张购物清单表结构就是清单的列商品名、数量、价格每一对括号就是一行条目。你用完之后把纸一揉不需要专门准备一个“购物清单文件夹”。2.2 为什么说 VALUES 构造的是“临时表”严格来说VALUES构造出来的数据集在数据库内部是一个派生表derived table或者内联视图。它不像物理临时表那样占用 tempdbSQL Server或者临时表空间也不涉及日志记录数据库执行完就释放。所以它天然具有“临时”的特性。我之前在 SQL Server 里做过一次粗略对比同样的数据量10万行级别用#temp物理临时表插入再查询和用VALUES构建派生表直接关联后者的额外开销小很多尤其在纯内存计算时两者的性能差距可以到几倍甚至十几倍。当然如果你的数据量达到百万行以上千万级别我不建议继续用VALUES硬扛那个场景下物理临时表或者表变量才是合理的。VALUES的最佳适用区间是“小数据量、临时计算、快速验证”这个边界要心里有数。2.3 一个容易被忽略的底层逻辑行构造器的类型推断用VALUES的时候数据库会自动推断每一列的数据类型。这里的规则是同一列的所有行的字面量类型会做“兼容合并”合并的结果就是这列最终的类型。举例来说SELECT * FROM (VALUES (1, 10), (2, 20)) AS t(id, val);第一行第二列是字符串10第二行第二列是数字20。在 SQL Server 里系统会把这列推断成int并尝试把10隐式转换成数字在 PostgreSQL 里也可能做类似处理。但如果某个值没法转换比如abc和20混在一起查询就会报错。这一点我在第 6 部分会有更多实战细节。所以我的建议是用VALUES造数据时尽量保持同一列的类型一致。哪怕需要不同类型也请统一用显式类型转换避免数据库自己猜。3. 临时数据集的核心玩法CTE、派生表和 INSERT INTO 三件套3.1 用 VALUES CTE 构建“逻辑临时表”告别反复建表大多数场景下我喜欢把VALUES和公共表表达式CTE配合使用。CTE 本身就能像临时表一样在一条 SQL 的范围内反复被引用而VALUES负责给 CTE 提供数据两者结合起来会非常顺手。WITH temp_data (order_id, customer_name, amount) AS ( SELECT * FROM (VALUES (1001, 张三, 58.50), (1002, 李四, 120.00), (1003, 王五, 35.80) ) AS td(order_id, customer_name, amount) ) SELECT customer_name, SUM(amount) AS total_amount FROM temp_data GROUP BY customer_name;这里我建了一个叫temp_data的逻辑临时表它只在当前这条 SQL 语句执行期间存在。后续你可以继续 JOIN 其他真实表也可以在 CTE 里做多次计算。整个过程没有创建任何物理对象也不需要显式DROP非常适合做数据清洗、数据校验、报表口径验证这些场景。有一个细节值得注意在 SQL Server 里WITH temp_data (order_id, ...)这种在 CTE 名称后直接带列名列表的写法是支持的PostgreSQL 也支持。但 MySQL 8.0 里用 CTE 时列的别名应该写在 CTE 内部WITH temp_data AS ( SELECT * FROM (VALUES ROW(1001, 张三, 58.50), ROW(1002, 李四, 120.00) ) AS td(order_id, customer_name, amount) ) SELECT * FROM temp_data;两种差异不大写的时候注意一下兼容性就好。3.2 直接在 FROM 子句里用 VALUES 做“内联临时表”如果只是临时关联一个小集合不想写 CTE也可以直接把VALUES放在FROM后面当派生表用。这一招特别适合处理“代码集”类的需求比如根据一批指定 ID 过滤数据要保留原始顺序。SELECT t.id, t.sort_order FROM (VALUES (101, 3), (102, 1), (103, 2) ) AS t(id, sort_order) INNER JOIN products p ON p.product_id t.id ORDER BY t.sort_order;这段 SQL 就实现了一个常见需求按指定的 ID 顺序返回产品列表。原来你得写一堆CASE WHEN或者用CHARINDEX去处理自定义排序现在用VALUES把“ID-序号”对应表造出来再 JOIN代码可读性一下子高很多。我经常在接口的 ID 列表排序、批量状态更新这类场景里用它。3.3 用 VALUES 给已存在的临时表批量灌数据还有一种场景你已经有了物理临时表#temp或TEMPORARY TABLE需要用VALUES往里快速插入多行。SQL Server 从 2008 开始支持多行 VALUES 插入MySQL、PostgreSQL 更是默认支持。CREATE TABLE #temp_products ( product_code VARCHAR(20), price DECIMAL(10,2) ); INSERT INTO #temp_products (product_code, price) VALUES (SKU-A001, 19.99), (SKU-A002, 29.99), (SKU-B001, 9.99);这个操作之前我最常犯的错就是忘记在VALUES前加列名列表就直接INSERT INTO #temp_products VALUES...。虽然这样语法上也能过但一旦表的列顺序改动过数据就串位了。所以老规矩显式列名一定要写。这也是一个习惯问题养成好习惯能少踩很多坑。4. 格式不同别掉坑主流数据库 VALUES 语法差异速查很多人觉得自己会写 SQL Server 的VALUES到了 PostgreSQL 或者 MySQL 就懵了。原因是不同数据库对“行构造器”的语法支持并不完全一样。以下是我根据自己多数据库开发经验整理的差异点。4.1 SQL Server简单直接还可以用 VALUES 做 UPDATE 数据源SQL Server 里最标准的写法就是每一行括号加逗号隔开SELECT * FROM (VALUES (1, A), (2, B)) AS t(id, val);不需要在每行前写ROW关键字也没那么多装饰。SQL Server 还支持VALUES用在MERGE语句的USING子句里比如你要根据一个临时数据集去匹配更新目标表MERGE INTO products AS target USING (VALUES (101, 新版产品A, 25.00), (102, 新版产品B, 35.00) ) AS source(product_id, product_name, price) ON target.product_id source.product_id WHEN MATCHED THEN UPDATE SET product_name source.product_name, price source.price;这个技巧特别适合批量刷数据、做一次性数据订正。比起一条一条写UPDATE用VALUES构造数据集再MERGE效率和正确率都高不少。4.2 PostgreSQL需要 ROW 关键字且 VALUES 自带类型转换能力PostgreSQL 的写法稍微严一点推荐用ROW关键字显式构造行SELECT * FROM (VALUES ROW(1, Alice), ROW(2, Bob) ) AS t(id, name);不带ROW也支持但带上更清晰。PostgreSQL 的VALUES列表有一个特性如果同一列出现不同类型它会用UNION的类型推断规则去寻找“公共类型”。比如VALUES (1, 2024-01-01), (2, 2024-01-02)第二列会被自动识别为date类型这一点比 SQL Server 要聪明一些。4.3 MySQL8.0 才完整支持 VALUES 表构造器老版本要绕路MySQL 在 8.0 里终于支持了独立的VALUES构造表SELECT * FROM (VALUES ROW(1, Alice), ROW(2, Bob) ) AS t(id, name);但如果你还在维护 5.7 及更老的库用不了这种写法。老版本的替代方案是用多个SELECT ... UNION ALL来模拟虽然丑一点但效果一样SELECT 1 AS id, Alice AS name UNION ALL SELECT 2, Bob;4.4 Oracle从 23c 开始支持老版本用 FROM dual 拼接Oracle 对VALUES表构造器的支持比 MySQL 还晚23c 才开始原生支持较好。老版本里最常用的是SELECT ... FROM dual加UNION ALLSELECT 1 AS id, Alice AS name FROM dual UNION ALL SELECT 2, Bob FROM dual;Oracle 也有一种SYS.ODCIVARCHAR2LIST或SYS.ODCINUMBERLIST构造集合的写法配合TABLE()函数也能当表用但可读性和通用性都比较差。如果你在 Oracle 环境里非要用VALUES风格建议升级到 23c或者在旧版本老老实实用UNION ALL。我把主要差异整理成了下面的速查表方便大家查阅数据库VALUES 独立使用多行 INSERT推荐写法SQL Server支持无需 ROW支持(VALUES (1,A),(2,B)) AS t(id,val)PostgreSQL支持推荐 ROW支持(VALUES ROW(1,A),ROW(2,B)) AS t(id,val)MySQL 8.0支持必须 ROW支持(VALUES ROW(1,A),ROW(2,B)) AS t(id,val)MySQL 5.7不支持支持用SELECT ... UNION ALL代替Oracle 23c支持支持类似标准 SQLOracle 旧版不支持老版本仅支持单行用FROM dualUNION ALL代替5. VALUES 的进阶应用场景我用它解决过的几个实际问题5.1 用 VALUES 生成连续数字序列替代递归 CTE 的笨办法有时候我们想要一张“1 到 1000 的连续数字表”用于统计连续日期、补齐缺失行或做排列组合。很多人的第一反应是写递归 CTE但用VALUES加上交叉连接CROSS JOIN也可以非常高效地生成WITH nums AS ( SELECT * FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS d(n) ) SELECT a.n b.n * 10 c.n * 100 AS num FROM nums a CROSS JOIN nums b CROSS JOIN nums c ORDER BY num;这段代码生成了 0 到 999 的 1000 个数字利用了三组数字的笛卡尔积。整个过程没有递归、没有循环执行效率很高。有了这张数字表你可以去补日期序列、做分页模拟、按数字拆字符串等等。我自己做日历报表的时候经常用这个思路先构造一个完整的日期序列再左连接业务表这样就能保证报表上每一天都有记录不会出现日期断层。5.2 在存储过程里用 VALUES 构造参数集合避免拼装 SQL有些存储过程需要接收一个“ID 数组”但存储过程的参数不好直接传列表。以前很多人选择把 ID 列表拼成一个逗号分隔的字符串然后在存储过程里用字符串拆分函数处理不仅慢而且容易出错。后来我习惯让调用方直接把数据以VALUES的形式放到 SQL 里当成派生表来传参CREATE PROCEDURE usp_GetProductInfo AS BEGIN SELECT ... FROM products p INNER JOIN (VALUES (1001, 5), (1002, 3), (1003, 8) ) AS param(product_id, quantity) ON p.product_id param.product_id; END;这里的param表就充当了“输入参数集合”的角色。如果你的 ORM 框架支持构造这种 SQL那么很多批量操作都会变得非常简单。5.3 配合字符串拆分函数生成“动态 IN 列表”有时候我们传入的是1,5,9,20这种字符串直接 IN 肯定不行。结合VALUES和递归或拆分函数可以做出一套比较优雅的拆解方案这里以 SQL Server 的STRING_SPLIT为例DECLARE ids VARCHAR(100) 1,5,9,20; SELECT value AS id FROM STRING_SPLIT(ids, ,) WHERE TRY_CAST(value AS INT) IS NOT NULL;如果你用的是更老的版本没有STRING_SPLIT也可以用VALUES构造一个“十位数表”来配合SUBSTRING拆分。思路本质一样先有一张数字序列表再对字符串按位置切片。我在做一个老的 2008R2 项目时就是靠这个思路解决了“字符串 ID 列表如何关联查询”的问题。5.4 数据清洗时用 VALUES 建映射表一步完成翻译转换做数据清洗时经常遇到编码值和显示值不一致的情况比如数据库里存的是0/1要显示成男/女或者状态字段存的N/P/F要翻译成中文。常规做法是左连接一张字典表但字典表要维护。如果只是临时做一次清洗完全可以用VALUES现场建映射SELECT raw.user_code, raw.status_code, mapping.status_name FROM user_raw_data raw LEFT JOIN (VALUES (N, 新建), (P, 进行中), (F, 已完成) ) AS mapping(status_code, status_name) ON raw.status_code mapping.status_code;这招在处理一次性数据迁移、临时报表、接口联调时特别香。不用事先建字典表也不用提交数据库变更脚本一条 SQL 自己就闭环了。6. 我实际踩过的 VALUES 相关的几个坑6.1 列数不一致直接报错最常见的问题是行的列数不一致。比如SELECT * FROM (VALUES (1, A), (2) ) AS t(id, val);第二行只有一列数据库会直接报“列数不匹配”的错误。这个问题在新手身上出现得特别多而且有时候肉眼扫很难发现。我的经验是写完 VALUES 列表之后数一下每行的括号内有几个值尤其是复制粘贴改数据的时候特别容易删多或删少。我自己有几次就是从 10 行数据里删掉中间一行结果把一列顺手删了查了半天才反应过来。6.2 列类型冲突与隐式转换陷阱前面提到过类型推断的问题。再举一个我实际遇到的例子有次我把一个参数NULL放进了VALUES结果 SQL Server 推断这一列为int后来想 CAST 成varchar时报错。正确做法是在NULL旁边用显式类型转换SELECT * FROM (VALUES (1, CAST(NULL AS VARCHAR(20))), (2, Alice) ) AS t(id, name);把NULL单独放在VALUES里时数据库不知道它是字符串、数字还是日期所以最好用CAST或CONVERT明确指定类型。这一点在“向临时表插入数据后继续 JOIN”时尤其重要不然等到运行到后面才发现类型对不上排查成本就高了。6.3 关键字冲突和别名缺失很多数据库要求派生表必须有别名。我在 PostgreSQL 里曾经写过SELECT * FROM (VALUES (1, 2));直接报错必须给派生表加别名SELECT * FROM (VALUES (1, 2)) AS t(a, b);另外如果列名用了数据库的保留字比如order、group、key记得用反引号MySQL或双引号标准 SQL包裹。SQL Server 用方括号[order]PostgreSQL 用双引号order。这个坑我在 MySQL 里踩过好几次因为key太常见了不注意就会变成语法错误。6.4 太长的 VALUES 列表会导致 SQL 难以维护尽管VALUES很好用但你别把它撑成一个几百行的巨型列表。我见过有人在代码里写了一个 2000 行数据的 VALUES 集合结果阅读、调试、排错都极其痛苦。遇到这种情况建议采用临时表 批量插入或者从 Excel 生成 INSERT 脚本。VALUES适合几十行以内的小数据集合一旦超过这个量级我就开始考虑物理临时表了。这个度每个人可以结合自己的习惯但别让一段 SQL 膨胀成了“数据文件”。6.5 不同数据库对 NULL 排序的差异影响如果VALUES造出来的临时表里有 NULL在后面做排序或分组的时候不同数据库的行为不一样。SQL Server 中 NULL 默认排在前面PostgreSQL 默认排在最后MySQL 中 NULL 也排在前面。这有时会打乱你的预期结果。如果是做报表最好明确用ORDER BY ... NULLS LAST或COALESCE把 NULL 转成默认值避免环境不同导致结果不同。7. 常见问题与排查技巧实录我在技术群里经常看到有人问 VALUES 相关的问题挑几个典型的把排查思路也列出来做成一个速查表方便大家以后直接照着查。问题现象可能原因解决思路The column prefix does not match with a table name派生表没加别名在 VALUES 闭合括号后加上AS t(列1, 列2)VALUES list has different number of columns某行列数比其他行少或多逐行数括号内字段数量保证一致Error converting data type varchar to int同一列混了数字和字符串统一类型必要时用CAST/TRY_CASTIncorrect syntax near ,多了或少了括号检查逗号和括号的配对推荐编辑器高亮功能Null value is eliminated by aggregate or other SET operation聚合时遇到 NULL用ISNULL/COALESCE先处理默认值结果顺序不符合预期VALUES表没有自带顺序语义额外加一列序号按序号ORDER BYMySQL 5.7 里语法直接报错老版本不支持 VALUES 表构造器改成SELECT ... UNION ALL这里再单独说一个很隐蔽的坑在 SQL Server 里用过大的数字直接放入VALUES可能会被推断为numeric或decimal导致精度变化。比如(100000000000000000000)这种超长数字直接放进 VALUES再和其他bigint列做 JOIN 时类型不匹配会引发隐式转换可能导致索引失效。解决办法还是老办法显式CAST成目标类型。另外如果发现VALUES构造的表在查询计划里出现了“Table Scan”或“Constant Scan”这是正常的数据量小时无需担心。如果数据量大导致查询变慢那是VALUES用错了场景要果断换成临时表。8. 实践中的选择判断什么时候用 VALUES什么时候老实建临时表用VALUES构造临时数据有很多优势但它不是万能的。我给自己定了几条原则供大家参考其一数据量在几百行以内的、临时验证性质的优先用VALUES。不需要 DDL不需要考虑事务和日志SQL 跑完就完事。这也是它最舒服的场景。其二数据量在上千到几万行、而且后续会多次引用老老实实建临时表。物理临时表有统计信息可以被索引可以被多个批次访问性能和稳定性都更好。其三数据从别的表查出来而不是手写的那直接用SELECT ... INTO或者CREATE TABLE AS别把数据复制粘贴成VALUES那样又慢又容易出错。其四需要跨多条 SQL 语句共享数据集时用临时表或表变量别指望VALUES能跨语句存活。VALUES的“临时性”仅限于它所在的那一条 SQL 语句内部。这套判断逻辑说白了就是看数据量和生命周期。生命周期短、量小就VALUES生命周期长、量大就临时表。没有绝对的哪个更好只有合不合适。最后分享一个我自己的使用习惯我经常用 VALUES 构建一个“期望结果表”然后把真实查询结果跟它对比来做快速数据校验。比如算一个汇总口径我先把用手工算好的答案写成 VALUES 表再EXCEPT一下真实结果两者不一致立刻能看出来。这个技巧帮我节省了大量核对时间也算是对VALUES的一个独创用法吧。希望这些内容对你也有实实在在的帮助。