oracle - 内联视图中的 ORA-1427
问题描述
我们的 Prod 环境中有一个查询失败,该查询因 ora-01427 单行子查询返回更多行而失败。这是oracle 11g 数据库。如下查询。这个查询运行良好,直到我们用 SQ3 添加最后的左外连接,一旦添加它会在一段时间后失败并出现 ORA-1427。
select c1,c2..c8 from
t1 left join
(subquery with joins)SQ1
left join
(subquery with joins)SQ2
left join
(subquery with joins)SQ4
left join
(subquery with joins)SQ5
left join
(SELECT DISTINCT MAX(c1) c1, c2, c3, c4, c5,c6
FROM s1.t1 WHERE c2='NY' AND c7<'2' AND c8='Y'
GROUP BY c1, c2, c3, c4, c5,c6) SQ3 ON sq3.c3=t1.c3
AND sq3.c8=t1.c8
AND sq3.c7=t2.c6
AND sq3.c6 <'2'
AND sq3.c4='Y'
当我使用 WITH 子句重写此查询时,它运行良好,见下文。当下面的第二个执行没有改变逻辑时,为什么第一个查询失败的任何想法。
with
(SELECT DISTINCT MAX(c1) c1, c2, c3, c4, c5,c6
FROM s1.t1 WHERE c2='NY' AND c7<'2' AND c8='Y'
GROUP BY c1, c2, c3, c4, c5,c6) as SQ3
select c1,c2..c8 from
t1 left join
(subquery with joins)SQ1
left join
(subquery with joins)SQ2
left join
(subquery with joins)SQ4
left join
(subquery with joins)SQ5
left join
SQ3 ON sq3.c3=t1.c3
AND sq3.c8=t1.c8
AND sq3.c7=t2.c6
AND sq3.c6 <'2'
AND sq3.c4='Y'
解决方案
Mira a ver esto de agrupar por la columna del agregado, no parece correcto (SELECT DISTINCT MAX(c1) c1 , c2, c3, c4, c5,c6 FROM s1.t1 WHERE c2='NY' AND c7<'2' AND c8='Y' **
按c1分组
**
推荐阅读
- javascript - Redux 操作打字稿返回类型不起作用
- sql - 从相当老的 IBM iseries AS400 DB2 查询数据(日期格式问题)
- python - 如何在 Pandas 上通过 group by 应用累积聚合函数?
- regex - 正则表达式(Bigquery)从 STRING 获取特定值
- django - 设置特定查询集的限制
- c# - DDD - DomainDrivenDesign 和解析日期
- vb.net - VB.NET 4.5 应用程序在 Windows 10 操作系统中的性能下降
- javascript - 多步骤表单 - 如何显示之前步骤的数据?
- angular - Angular 8:Google Maps API 对中国的支持
- angular - Angular 中的 URL 更改后组件不更新