sql - 使用“newid()”将单行子查询与列交叉连接会导致每一行都有不同的 GUID
问题描述
抽象的
像这样的查询
SELECT *
FROM elbat t
CROSS JOIN (SELECT newid() guid) x;
在 SQL Server 中会产生一个结果,其中每一行都有不同的 GUID,而不是在整个结果中每一行都有一个共同的 GUID。如何为结果的所有行设置一个 GUID(不使用变量或(临时)表)?
设置
请考虑 SQL Server 数据库中的下表。
CREATE TABLE elbat
(id integer);
INSERT INTO elbat
VALUES (1);
INSERT INTO elbat
VALUES (2);
INSERT INTO elbat
VALUES (3);
INSERT INTO elbat
VALUES (4);
INSERT INTO elbat
VALUES (5);
INSERT INTO elbat
VALUES (6);
让我们运行以下查询。
SELECT *
FROM elbat t
CROSS JOIN (SELECT newid() guid) x;
这是一个db<>fiddle和一个SQL Fiddle来查看它的运行情况。
问题
令我惊讶的是,结果每一行都有不同的 GUID。例如:
id | guid
-: | :-----------------------------------
1 | ad146af7-9ebd-4521-a440-47c7dea6a1d4
2 | ce24fbb8-af64-480c-8c46-1e03187642c5
3 | 14509451-9b1d-49e9-8da2-c691947ae805
4 | 37a86339-e352-486f-b541-92798540599f
5 | cbee1a8e-02ce-4915-8d2c-ef5db299d8c8
6 | d491275b-4ebb-461b-94e2-93b47e7d2348
这让我很困惑。我希望每一行在整个结果集中都有相同的 GUID。例如:
id | guid
-: | :-----------------------------------
1 | cbee1a8e-02ce-4915-8d2c-ef5db299d8c8
2 | cbee1a8e-02ce-4915-8d2c-ef5db299d8c8
3 | cbee1a8e-02ce-4915-8d2c-ef5db299d8c8
4 | cbee1a8e-02ce-4915-8d2c-ef5db299d8c8
5 | cbee1a8e-02ce-4915-8d2c-ef5db299d8c8
6 | cbee1a8e-02ce-4915-8d2c-ef5db299d8c8
我当然明白,GUID 会因调用而异。但我不明白为什么当我交叉加入单个 GUID 并且没有将newid()
调用放入投影列列表时它会逐行变化。
附加信息
我在小提琴平台上的所有可用版本以及本地 Microsoft SQL Server 2014(12.0.2269.0(X64),Express)上都尝试了这个。结果在任何地方都是相同的(当然只有 GUID 改变)。
质疑我对连接的理解,我还用等效的设置和查询对其他 DBMS 进行了一些测试。
- (DDL 和 DML 对于所有这些都保持不变。)
Postgres:
SELECT * FROM elbat t CROSS JOIN (SELECT uuid_generate_v4() guid) x;
甲骨文:
SELECT * FROM elbat t CROSS JOIN (SELECT sys_guid() guid FROM dual) x;
玛丽亚数据库:
SELECT * FROM elbat t CROSS JOIN (SELECT uuid() guid) x;
MySQL:
SELECT * FROM elbat t CROSS JOIN (SELECT uuid() guid) x;
所有这些其他 DBMS 都会产生我实际期望的结果——结果的所有行中都有一个公共 GUID。
我还尝试更改查询。虽然无济于事。
- 将子查询放在 CTE 中。
- 我尝试从子查询中的物理表中进行选择,而不是在
SELECT
没有FROM
, usingTOP
和主键的情况下进行选择。 - 使用隐式交叉连接 (
FROM elbat, (SELECT newid() ...)
)。 - 使用
CROSS APPLY
.
查找文档,我在任何地方都找不到这种行为。
问题
为什么 SQL Server 的行为与所有其他(经过测试的)DBMS(在这方面)不同,有没有办法按预期获得结果(不使用变量或(临时)表)?
(注意:我知道我可以使用初始化的变量并将newid()
其放在投影列中。但是当我试图避免这样的变量时,问题实际上出现了。我实际上想寻找一个无变量、仅查询的解决方案对于“随机订购表,但有例外”。)
解决方案
我对 SQL Server 的行为感到非常惊讶。我没有意识到它一遍又一遍地重新评估这些子查询。我怀疑原因是优化: 中的表达式cross join
实际上被移动到读取数据的节点,所以函数被一遍又一遍地调用。
无论如何,我认为这是错误的。这种优化应该认识到这newid()
是一个不稳定的函数并相应地进行调整。
经过一些实验,我发现order by
在子查询中确实会导致它只被评估一次。所以,这就是你想要的:
select *
from elbat cross join
(select top (1) newid() as guid
order by guid
) x;
另一个符合您期望的版本:
select *
from elbat cross join
(select max(newid()) as guid
) x;
顺便说一句,后一个版本也适用于select
:
select *, (select max(newid())) as guid
from elbat ;
在这种情况下,我希望对每一行评估一次子查询。去搞清楚。
推荐阅读
- xml - 在python中解析具有空属性的XML文件
- python - 加载权重时的 Keras ValueError
- cordova - 我需要 Ionic 安全存储插件吗?
- c++ - 为什么 C++ 标准禁止 const 元素的容器?
- android - 滚动视图不适用于约束布局
- java - HTTPServletRequest 对象 - 封装 - 最佳实践
- java - 无法反序列化为具体的 Java 类型
- wordpress - 更新旧插件
- pytest - 如何使用 tox+pytest 启用对 DeprecationWarning 和 PendingDeprecationWarning 的测试
- typescript - 定义某种类型的通用打字稿排序函数