gpt4 book ai didi

oracle - 如何将 Oracle 的 LISTAGG 函数与多值一起使用?

转载 作者:行者123 更新时间:2023-12-01 12:10:21 28 4
gpt4 key购买 nike

我有一个“项目”表,如下所示:

ITEM_NO     ITEM_NAME   
1 Book
2 Pen
3 Sticky Notes
4 Ink
5 Corrector
6 Ruler

在另一个“EMP_ITEMS”表中,我有以下内容:
EMPLOYEE        ITEMS_LIST   
John 1,2
Mikel 5
Sophia 2,3,6
William 3,4
Daniel null
Michael 6

输出必须是这样的:
EMPLOYEE        ITEMS_LIST      ITEM_NAME   
John 1,2 Book,Pen
Mikel 5 Corrector
Sophia 2,3,6 Pen,Sticky Notes,Ruler
William 3,4 Sticky Notes,Ink
Daniel null null
Michael 6 Ruler

我使用了以下查询:
SELECT e.EMPLOYEE,e.ITEMS_LIST, LISTAGG(i.ITEM_NAME, ',') WITHIN GROUP (ORDER BY i.ITEM_NAME) ITEM_DESC 
FROM EMP_ITEMS e
INNER JOIN ITEMS i ON i.ITEM_NO = e.ITEMS_LIST
GROUP BY e.EMPLOYEE,e.ITEMS_LIST;

但是有一个错误:

ORA-01722: invalid number

最佳答案

But there is an error: ORA-01722: invalid number



那是因为您的 ITEMS_LIST是一个由数字和逗号字符组成的字符串,实际上不是数字列表,您正在尝试将单个项目编号与项目列表进行比较。

而是将其视为字符串以查找子字符串匹配。为此,您需要将字符串括在分隔符中,并比较其中一个是否是另一个的子字符串:

SQL Fiddle

Oracle 11g R2 架构设置 :
CREATE TABLE Items ( ITEM_NO, ITEM_NAME ) As
SELECT 1, 'Book' FROM DUAL UNION ALL
SELECT 2, 'Pen' FROM DUAL UNION ALL
SELECT 3, 'Sticky Notes' FROM DUAL UNION ALL
SELECT 4, 'Ink' FROM DUAL UNION ALL
SELECT 5, 'Corrector' FROM DUAL UNION ALL
SELECT 6, 'Ruler' FROM DUAL;

CREATE TABLE emp_items ( EMPLOYEE, ITEMS_LIST ) AS
SELECT 'John', '1,2' FROM DUAL UNION ALL
SELECT 'Mikel', '5' FROM DUAL UNION ALL
SELECT 'Sophia', '3,2,6' FROM DUAL UNION ALL
SELECT 'William', '3,4' FROM DUAL UNION ALL
SELECT 'Daniel', null FROM DUAL UNION ALL
SELECT 'Michael', '6' FROM DUAL;

查询 1 :
SELECT e.employee,
e.items_list,
LISTAGG( i.item_name, ',' )
WITHIN GROUP (
ORDER BY INSTR( ','||e.items_list||',', ','||i.item_no||',' )
) AS item_names
FROM emp_items e
LEFT OUTER JOIN
items i
ON ( ','||e.items_list||',' LIKE '%,'||i.item_no||',%' )
GROUP BY e.employee, e.items_list

Results :
| EMPLOYEE | ITEMS_LIST |             ITEM_NAMES |
|----------|------------|------------------------|
| John | 1,2 | Book,Pen |
| Mikel | 5 | Corrector |
| Daniel | (null) | (null) |
| Sophia | 3,2,6 | Sticky Notes,Pen,Ruler |
| Michael | 6 | Ruler |
| William | 3,4 | Sticky Notes,Ink |

关于oracle - 如何将 Oracle 的 LISTAGG 函数与多值一起使用?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/52056969/

28 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com