sql - 与 group by 一起使用时,cross apply 的行为很奇怪
问题描述
我不明白为什么在这种情况下列correct
和incorrect
具有不同的值。老实说 - 这是有效的 SQL 似乎很奇怪,因为该cross apply
部分不是聚合函数,因为group by
.
with Foo as (
select 1 as id, 'foo_1' as txt from Dual union all
select 2, 'foo_2' from Dual),
Bar as (
select 1 as id, 'bar_1' as txt, 1 as foo_id from Dual union all
select 2, 'bar_2', 1 from Dual union all
select 3, 'bar_3', 1 from Dual union all
select 4, 'bar_4', 2 from Dual)
select
f.id,
listagg(b.txt, ', ') within group (order by b.id) as correct,
incorrect
from Foo f
join Bar b on f.id = b.foo_id
cross apply (
select
listagg(txt, ', ') within group (order by id) as incorrect
from Bar
where foo_id = f.id)
group by f.id
order by f.id;
结果是:
ID CORRECT INCORRECT
1 bar_1, bar_2, bar_3 bar_4
2 bar_4 bar_4
但是当我确实删除了该group by
部分并添加distinct
以下内容时:
with Foo as (
select 1 as id, 'foo_1' as txt from Dual union all
select 2, 'foo_2' from Dual),
Bar as (
select 1 as id, 'bar_1' as txt, 1 as foo_id from Dual union all
select 2, 'bar_2', 1 from Dual union all
select 3, 'bar_3', 1 from Dual union all
select 4, 'bar_4', 2 from Dual)
select distinct
f.id,
incorrect_now_correct
from Foo f
join Bar b on f.id = b.foo_id
cross apply (
select
listagg(txt, ', ') within group (order by id) as incorrect_now_correct
from Bar
where foo_id = f.id)
order by f.id;
我确实得到以下结果:
ID INCORRECT_NOW_CORRECT
1 bar_1, bar_2, bar_3
2 bar_4
这是我期望从incorrect
第一个查询中的列得到的结果。
使用子句时是否对group by
子句有不同的处理group by
?到目前为止,我只认为它更像是一个带有子查询的智能连接。添加incorrect
到该group by
部分使其按应有的方式工作,没有它,行为就像第一个查询的结果中描述的那样。它编译、运行并返回一个有点随机的值。
编辑:数据库版本是Oracle Database 12c Enterprise Edition 12.1.0.2.0
解决方案
推荐阅读
- asp.net-core - Is authenticating a service-type user as an end user bad?
- javascript - Node Express joi validation failed when I added .when()
- java - 是否可以在后端使用多个框架(Spring boot + Django)?
- javascript - 多个 XMLHttpRequests - 从多个响应中检索数据
- reactjs - 仅将 React 与函数一起使用有什么好处吗?
- mongoose - Mongoose countDocuments() 返回查询
- blazor - Blazor WASM 在未通过身份验证时重定向到登录 - 不显示布局
- html - SVG 组子项未按预期转换
- c# - 使用 Entity Framework Core 重用查询
- powershell - Powershell:在 Active Directory 中检索 LogonDate