子查询即使使用 IN 语句 - Oracle SQL，也返回超过 1 行 - Subquery Returns more than 1 row even using IN statement

我正在使用两个表，第一个，purchases下面的(注意，这是purchases表的剪辑：

|    ID   |    Date   | Value | Type | Satisfied By |
|:-------:|:---------:|:-----:|:----:|:------------:|
| SALE100 |  1/1/2019 |   -5  |  OUT |              |
| SALE201 |  1/9/2019 |  -10  |  OUT |              |
| SALE203 | 2/22/2019 |   -1  |  OUT |              |
| SALE205 | 3/14/2019 |   -1  |  OUT |              |

我正在尝试确定另一个表中的哪些MAKE项目makes满足这些销售。

|    ID   |    Date    | Value | Needed For |
|:-------:|:----------:|:-----:|:----------:|
| MAKE300 | 12/24/2018 |   5   |   SALE100  |
| MAKE301 |  1/3/2019  |   3   |   SALE201  |
| MAKE399 |  1/5/2019  |   5   |   SALE201  |
| MAKE401 |  1/7/2019  |   3   |   SALE201  |
| MAKE401 |  1/7/2019  |   3   |   SALE203  |
| MAKE912 |  2/1/2019  |   1   |   SALE205  |

我正在尝试编写一个查询，使我能够确定makes表中的哪个ID或IDs满足我的销售额。

如果它们LISTAGG，我的最终结果看起来像：

|    ID   |    Date   | Value | Type |        Satisfied By       |
|:-------:|:---------:|:-----:|:----:|:-------------------------:|
| SALE100 |  1/1/2019 |   -5  |  OUT |          MAKE300          |
| SALE201 |  1/9/2019 |  -10  |  OUT | MAKE301, MAKE399, MAKE401 |
| SALE203 | 2/22/2019 |   -1  |  OUT |          MAKE401          |
| SALE205 | 3/14/2019 |   -1  |  OUT |          MAKE912          |

但是，在编写以下代码行时：

(SELECT LISTAGG(makes.id, ', ') WITHIN GROUP (ORDER BY NULL) FROM makes WHERE purchased.id = needed_for.id) ELSE NULL END AS Satisfied_By

导致错误指出：

ORA-01489：字符串串联的结果太长 01489. 00000 - "字符串串联的结果太长">

我还尝试了以下查询来获得这样的结果(这是理想的)：

|    ID   |    Date   | Value | Type | Satisfied By |
|:-------:|:---------:|:-----:|:----:|:------------:|
| SALE100 |  1/1/2019 |   -5  |  OUT |    MAKE300   |
| SALE201 |  1/9/2019 |  -10  |  OUT |    MAKE301   |
| SALE201 |  1/9/2019 |  -10  |  OUT |    MAKE399   |
| SALE201 |  1/9/2019 |  -10  |  OUT |    MAKE401   |
| SALE203 | 2/22/2019 |   -1  |  OUT |    MAKE401   |
| SALE205 | 3/14/2019 |   -1  |  OUT |    MAKE912   |
CASE WHEN Type = 'OUT' THEN
(SELECT 
makes.id
FROM
makes
WHERE
makes.id IN (
SELECT
makes.id
FROM
makes
WHERE
sales.id = purchases.id
)) ELSE NULL END AS Satisfied_By

这会产生

ORA-01427：单行子查询返回多行 01427. 00000 - "单行子查询返回多行">

我在堆栈溢出上发现了许多此错误的示例，这是我从该来源采用IN方法的地方，但我仍然收到错误。任何帮助，不胜感激。

您的"理想"结果是一个简单的连接：

select p.id, p.dt, p.value, p.type, m.id as satisfied_by
from purchases p
join makes m on m.needed_for = p.id;

如果数据中可能，您可能希望将其设为left join以防不匹配。

快速演示您的数据：

-- CTEs for sample data
with purchases (id, dt, value, type, satisfied_by) as (
select 'SALE100', date '2019-01-01', -5, 'OUT', null from dual
union all select 'SALE201', date '2019-01-09', -10, 'OUT', null from dual
union all select 'SALE203', date '2019-02-22', -1, 'OUT', null from dual
union all select 'SALE205', date '2019-03-14', -1, 'OUT', null from dual
),
makes (id, dt, value, needed_for) as (
select 'MAKE300', date '2018-12-24', 5, 'SALE100' from dual
union all select 'MAKE301', date '2019-01-03', 3, 'SALE201' from dual
union all select 'MAKE399', date '2019-01-05', 5, 'SALE201' from dual
union all select 'MAKE401', date '2019-01-07', 3, 'SALE201' from dual
union all select 'MAKE401', date '2019-01-07', 3, 'SALE203' from dual
union all select 'MAKE912', date '2019-02-01', 1, 'SALE205' from dual
)
-- actual query
select p.id, p.dt, p.value, p.type, m.id as satisfied_by
from purchases p
left join makes m on m.needed_for = p.id;
ID      DT              VALUE TYP SATISFIED_BY                  
------- ---------- ---------- --- ------------------------------
SALE100 2019-01-01         -5 OUT MAKE300                       
SALE201 2019-01-09        -10 OUT MAKE301                       
SALE201 2019-01-09        -10 OUT MAKE399                       
SALE201 2019-01-09        -10 OUT MAKE401                       
SALE203 2019-02-22         -1 OUT MAKE401                       
SALE205 2019-03-14         -1 OUT MAKE912

listagg版本也相当简单：

select p.id, p.dt, p.value, p.type,
listagg(m.id, ', ') within group (order by m.id) as satisfied_by
from purchases p
left join makes m on m.needed_for = p.id
group by p.id, p.dt, p.value, p.type;
ID      DT              VALUE TYP SATISFIED_BY                  
------- ---------- ---------- --- ------------------------------
SALE100 2019-01-01         -5 OUT MAKE300                       
SALE201 2019-01-09        -10 OUT MAKE301, MAKE399, MAKE401     
SALE203 2019-02-22         -1 OUT MAKE401                       
SALE205 2019-03-14         -1 OUT MAKE912

从你的代码片段中并不清楚你做错了什么，但看起来你正确地关联了你的子查询;但是你并不真正需要它们......如果您已经正确关联了listagg版本的子查询，那么您实际上可能只是在真实数据中有太多匹配项;要么是这样，要么是子查询返回的数据比应有的多，并且聚合所有这些数据都打破了大小限制。

子查询的"缺失"部分是我使用CASE WHEN TYPE = 'OUT' THEN，所以没有什么花哨的，但这会限制我拥有的记录数量

您可以将其包含在连接条件中：

from purchases p
left join makes m on (p.type = 'OUT' and m.needed_for = p.id)

您仍然可以对listagg方法使用子查询：

select p.id, p.dt, p.value, p.type,
(
select listagg(m.id, ', ') within group (order by m.id) 
from makes m
where m.needed_for = p.id
-- and p.type = 'OUT'
) as satisfied_by
from purchases p;

实际上可能是您正在做的事情 - 目前还不完全清楚该条件是否等同于您展示的purchased.id = needed_for.id。如果您仍然从中获取 ORA-01489，那么您也将从非子查询版本获得，并且您有太多匹配项，无法将聚合列表放入 4000 字节。如果它们都有效，那么我不确定拥有子查询的优势是什么 - 充其量 Oracle 优化器可能会使它们等效，但似乎更有可能性能会更差。不过，您需要使用真实环境和数据进行测试才能确定。

无论有没有in()，非 listagg 子查询都不起作用(它只是增加了另一个级别的子查询，没有实际效果)：

select p.id, p.dt, p.value, p.type,
(
select m.id
from makes m
where m.needed_for = p.id
-- and p.type = 'OUT'
) as satisfied_by
from purchases p;
ORA-01427: single-row subquery returns more than one row

。因为您知道并期望从该子查询中获得多行，至少对于某些购买而言。对于您的示例数据，如果您排除SALE201，这实际上确实有效，但这没有帮助。您试图将多个值塞入单个标量结果中，这是行不通的，这就是为什么您首先需要查看 listagg 的原因。

除了 @Tejash 演示的 xmlagg 变体外，您还可以将组合值作为集合获取，例如：

select p.id, p.dt, p.value, p.type,
cast(multiset(
select m.id
from makes m
where m.needed_for = p.id
-- and p.type = 'OUT'
) as sys.odcivarchar2list) as satisfied_by
from purchases p;
ID      DT              VALUE TYP SATISFIED_BY                                      
------- ---------- ---------- --- --------------------------------------------------
SALE100 2019-01-01         -5 OUT ODCIVARCHAR2LIST('MAKE300')                       
SALE201 2019-01-09        -10 OUT ODCIVARCHAR2LIST('MAKE301', 'MAKE399', 'MAKE401') 
SALE203 2019-02-22         -1 OUT ODCIVARCHAR2LIST('MAKE401')                       
SALE205 2019-03-14         -1 OUT ODCIVARCHAR2LIST('MAKE912')

。或者作为表类型集合，您在架构中定义了一个。不过，这可能更难使用，甚至离你的"理想"输出更远。这有点取决于什么将使用结果集以及如何使用。

您的第一个查询返回了以下错误：

ORA-01489：字符串串联的结果太长 01489. 00000 - "字符串串联的结果太长">

由于列中的串联"Satisfied_By"长度超过 4000 个字符。在连接VARCHAR列时，您需要使用XMLAGG更安全的一面。

您可以尝试以下查询：

-- DATA PREPARATION
with purchases (id, dt, value, type, satisfied_by) as (
select 'SALE100', date '2019-01-01', -5, 'OUT', null from dual
union all select 'SALE201', date '2019-01-09', -10, 'OUT', null from dual
union all select 'SALE203', date '2019-02-22', -1, 'OUT', null from dual
union all select 'SALE205', date '2019-03-14', -1, 'OUT', null from dual
),
makes (id, dt, value, needed_for) as (
select 'MAKE300', date '2018-12-24', 5, 'SALE100' from dual
union all select 'MAKE301', date '2019-01-03', 3, 'SALE201' from dual
union all select 'MAKE399', date '2019-01-05', 5, 'SALE201' from dual
union all select 'MAKE401', date '2019-01-07', 3, 'SALE201' from dual
union all select 'MAKE401', date '2019-01-07', 3, 'SALE203' from dual
union all select 'MAKE912', date '2019-02-01', 1, 'SALE205' from dual
)
-- actual query
SELECT
P.ID,
P.DT,
P.VALUE,
P.TYPE,
RTRIM(XMLAGG(XMLELEMENT(E, M.ID, ',').EXTRACT('//text()')
ORDER BY
M.ID
).GETCLOBVAL(), ',') AS SATISFIED_BY
FROM
PURCHASES P
LEFT JOIN MAKES M ON P.ID = M.NEEDED_FOR
GROUP BY
P.ID,
P.DT,
P.VALUE,
P.TYPE;

DB小提琴演示

干杯！！

子查询即使使用 IN 语句 - Oracle SQL，也返回超过 1 行

相关内容

最新更新

热门标签：

子查询 即使使用 IN 语句 - Oracle SQL，也返回超过 1 行

相关内容

最新更新

热门标签：

子查询即使使用 IN 语句 - Oracle SQL，也返回超过 1 行