group-by - Oracle SQL Listagg 使用 case 语句条件删除重复项
问题描述
我试图通过使用 listagg 来显示带有逗号分隔列表的重复列值,但得到错误为“不是单个函数组”。希望我能得到一些帮助。
下面是带有插入语句和数据的DDL脚本:
DROP TABLE dept CASCADE CONSTRAINTS;
DROP TABLE myrole CASCADE CONSTRAINTS;
DROP TABLE person CASCADE CONSTRAINTS;
DROP TABLE person_role CASCADE CONSTRAINTS;
CREATE TABLE dept (
id INTEGER NOT NULL,
dept VARCHAR2(50 CHAR)
);
INSERT INTO dept (
id,
dept
) VALUES (
1,
'Operations'
);
INSERT INTO dept (
id,
dept
) VALUES (
2,
'Research'
);
INSERT INTO dept (
id,
dept
) VALUES (
3,
'Accounts'
);
INSERT INTO dept (
id,
dept
) VALUES (
4,
'Sales'
);
ALTER TABLE dept ADD CONSTRAINT dept_pk PRIMARY KEY ( id );
CREATE TABLE myrole (
id INTEGER NOT NULL,
role VARCHAR2(50 CHAR)
);
INSERT INTO myrole (
id,
role
) VALUES (
1,
'JJJ'
);
INSERT INTO myrole (
id,
role
) VALUES (
2,
'Auth'
);
INSERT INTO myrole (
id,
role
) VALUES (
3,
'AAA'
);
INSERT INTO myrole (
id,
role
) VALUES (
4,
'MMM'
);
INSERT INTO myrole (
id,
role
) VALUES (
5,
'KKK'
);
INSERT INTO myrole (
id,
role
) VALUES (
6,
'BBB'
);
ALTER TABLE myrole ADD CONSTRAINT myrole_pk PRIMARY KEY ( id );
CREATE TABLE person (
id INTEGER NOT NULL,
person VARCHAR2(50 CHAR)
);
INSERT INTO person (
id,
person
) VALUES (
1,
'John'
);
INSERT INTO person (
id,
person
) VALUES (
2,
'Scott'
);
INSERT INTO person (
id,
person
) VALUES (
3,
'Ruth'
);
INSERT INTO person (
id,
person
) VALUES (
4,
'Smith'
);
INSERT INTO person (
id,
person
) VALUES (
5,
'Frank'
);
INSERT INTO person (
id,
person
) VALUES (
6,
'Martin'
);
INSERT INTO person (
id,
person
) VALUES (
7,
'Blake'
);
ALTER TABLE person ADD CONSTRAINT person_pk PRIMARY KEY ( id );
CREATE TABLE person_role (
id INTEGER NOT NULL,
person_id INTEGER NOT NULL,
role_id INTEGER NOT NULL,
dept_id INTEGER
);
INSERT INTO person_role (
id,
person_id,
role_id,
dept_id
) VALUES (
1,
1,
1,
NULL
);
INSERT INTO person_role (
id,
person_id,
role_id,
dept_id
) VALUES (
2,
2,
2,
NULL
);
INSERT INTO person_role (
id,
person_id,
role_id,
dept_id
) VALUES (
3,
2,
4,
1
);
INSERT INTO person_role (
id,
person_id,
role_id,
dept_id
) VALUES (
4,
2,
4,
2
);
INSERT INTO person_role (
id,
person_id,
role_id,
dept_id
) VALUES (
5,
3,
1,
NULL
);
INSERT INTO person_role (
id,
person_id,
role_id,
dept_id
) VALUES (
6,
3,
5,
NULL
);
INSERT INTO person_role (
id,
person_id,
role_id,
dept_id
) VALUES (
7,
4,
3,
NULL
);
INSERT INTO person_role (
id,
person_id,
role_id,
dept_id
) VALUES (
8,
5,
6,
NULL
);
INSERT INTO person_role (
id,
person_id,
role_id,
dept_id
) VALUES (
9,
6,
6,
3
);
INSERT INTO person_role (
id,
person_id,
role_id,
dept_id
) VALUES (
10,
6,
6,
2
);
INSERT INTO person_role (
id,
person_id,
role_id,
dept_id
) VALUES (
11,
6,
2,
NULL
);
INSERT INTO person_role (
id,
person_id,
role_id,
dept_id
) VALUES (
12,
7,
6,
4
);
INSERT INTO person_role (
id,
person_id,
role_id,
dept_id
) VALUES (
13,
7,
6,
4
);
ALTER TABLE person_role ADD CONSTRAINT person_role_pk PRIMARY KEY ( id );
ALTER TABLE person_role
ADD CONSTRAINT person_role_myrole_fk FOREIGN KEY ( myrole_id )
REFERENCES myrole ( id );
ALTER TABLE person_role
ADD CONSTRAINT person_role_person_fk FOREIGN KEY ( person_id )
REFERENCES person ( id );
CREATE SEQUENCE dept_seq START WITH 1 NOCACHE;
CREATE OR REPLACE TRIGGER dept_tr BEFORE
INSERT ON dept
FOR EACH ROW
WHEN ( new.id IS NULL )
BEGIN
:new.id := dept_seq.nextval;
END;
/
CREATE SEQUENCE myrole_seq START WITH 1 NOCACHE;
CREATE OR REPLACE TRIGGER myrole_tr BEFORE
INSERT ON myrole
FOR EACH ROW
WHEN ( new.id IS NULL )
BEGIN
:new.id := myrole_seq.nextval;
END;
/
CREATE SEQUENCE person_seq START WITH 1 NOCACHE;
CREATE OR REPLACE TRIGGER person_tr BEFORE
INSERT ON person
FOR EACH ROW
WHEN ( new.id IS NULL )
BEGIN
:new.id := person_seq.nextval;
END;
/
CREATE SEQUENCE person_role_seq START WITH 1 NOCACHE;
CREATE OR REPLACE TRIGGER person_role_tr BEFORE
INSERT ON person_role
FOR EACH ROW
WHEN ( new.id IS NULL )
BEGIN
:new.id := person_role_seq.nextval;
END;
/
通过使用@Koen Lostrie 提供的以下查询并添加我需要的列,我得到如下所示的输出:
SELECT p.person, r.role as myrole, d.dept,
CASE
WHEN rl.role_type = 1 AND r.role IN ('AAA','BBB') THEN 'Add'
WHEN rl.role_type = 0 AND r.role = 'Auth' THEN 'Remove'
END as myaccess
FROM person_role pr
JOIN person p ON p.id = pr.person_id
JOIN myrole r ON r.id = pr.role_id
JOIN (
SELECT p.id, MIN(CASE WHEN r.ROLE = 'Auth' THEN 0 WHEN r.ROLE in ('AAA','BBB') THEN 1 ELSE 2 END) as role_type
FROM person_role pr
JOIN person p ON p.id = pr.person_id
JOIN myrole r ON r.id = pr.role_id
GROUP BY p.id
) rl ON rl.id = pr.person_id
left join dept d on d.id = pr.dept_id
查询的输出:
+--------+--------+------------+----------+
| PERSON | MYROLE | DEPT | MYACCESS |
+--------+--------+------------+----------+
| John | JJJ | | |
| Scott | Auth | | Remove |
| Scott | MMM | Operations | |
| Scott | MMM | Research | |
| Ruth | JJJ | | |
| Ruth | KKK | | |
| Smith | AAA | | Add |
| Frank | BBB | | Add |
| Martin | AAA | Accounts | |
| Martin | AAA | Research | |
| Martin | Auth | | Remove |
| Blake | BBB | Sales | |
| Blake | BBB | Sales | Add |
+--------+--------+------------+----------+
现在我想显示基于PERSON和MYROLE列以逗号分隔的DEPT列值,预期的输出如下所示:
+--------+--------+---------------------+----------+
| PERSON | MYROLE | DEPT | MYACCESS |
+--------+--------+---------------------+----------+
| John | JJJ | | |
| Scott | Auth | | Remove |
| Scott | MMM | Operations,Research | |
| Ruth | JJJ | | |
| Ruth | KKK | | |
| Smith | AAA | | Add |
| Frank | BBB | | Add |
| Martin | AAA | Accounts,Research | |
| Martin | Auth | | Remove |
| Blake | BBB | Sales | Add |
+--------+--------+---------------------+----------+
我将 listagg 添加到现有查询但出现错误
SELECT p.person, r.role as myrole,
listagg(d.dept, ', ') within group (order by d.dept) as dept,
CASE
WHEN rl.role_type = 1 AND r.role IN ('AAA','BBB') THEN 'Add'
WHEN rl.role_type = 0 AND r.role = 'Auth' THEN 'Remove'
END as myaccess
FROM person_role pr
JOIN person p ON p.id = pr.person_id
JOIN myrole r ON r.id = pr.role_id
JOIN (
SELECT p.id, MIN(CASE WHEN r.ROLE = 'Auth' THEN 0 WHEN r.ROLE in ('AAA','BBB') THEN 1 ELSE 2 END) as role_type
FROM person_role pr
JOIN person p ON p.id = pr.person_id
JOIN myrole r ON r.id = pr.role_id
GROUP BY p.id
) rl ON rl.id = pr.person_id
left join dept d on d.id = pr.dept_id
错误地没有得到一个组。不知道如何修复。感谢任何帮助。
谢谢,
里查
解决方案
LISTAGG
是一个聚合函数。如果将其应用于列,则需要在查询中指定分组依据的列。通常这是所有没有聚合函数的列。我没有测试,因为 dept 表和 person_roles 表都没有样本数据,但这可能是问题所在
SELECT p.person, r.role as myrole, listagg(d.dept, ', ') within group (order by d.dept) as dept_list,
CASE
WHEN rl.role_type = 1 AND r.role IN ('AAA','BBB') THEN 'Add'
WHEN rl.role_type = 0 AND r.role = 'Auth' THEN 'Remove'
END as myaccess
FROM person_role pr
JOIN person p ON p.id = pr.person_id
JOIN myrole r ON r.id = pr.role_id
JOIN (
SELECT p.id, MIN(CASE WHEN r.ROLE = 'Auth' THEN 0 WHEN r.ROLE in ('AAA','BBB') THEN 1 ELSE 2 END) as role_type
FROM person_role pr
JOIN person p ON p.id = pr.person_id
JOIN myrole r ON r.id = pr.role_id
GROUP BY p.id
) rl ON rl.id = pr.person_id
left join dept d on d.id = pr.dept_id
GROUP BY
p.person,
r.role,
CASE
WHEN rl.role_type = 1 AND r.role IN ('AAA','BBB') THEN 'Add'
WHEN rl.role_type = 0 AND r.role = 'Auth' THEN 'Remove'
END
ORDER BY p.person
推荐阅读
- wordpress - Wordpress/Gutenberg 侧边栏向右挤压
- neo4j - Neo4j - 每当使用任何带有 GET 或 POST 的查询时都会遇到 FetchURLError
- python - 如何在 python 中显示/保存从服务器收到的带有 http 请求的 xlsx 文件?
- android - Amplify.DataStore.query 正在返回由所有用户创建的数据,而不仅仅是所有者
- vba - 如何在 VBA 中使用 Selenium 禁用 navigator.webdriver?
- amazon-web-services - AWS SQS Lambda 一次处理 n 个文件
- javascript - 为什么javascript getDate不是从0开始
- logstash - Grok 模式在 grok 调试器中工作正常,但在使用 logstash 运行时相同的模式不起作用
- docker-compose - 反向代理背后的 Keycloak 混合了内部和外部地址
- node.js - 通过 GET 请求将自定义标头传递到 API Gateway 时,它返回 No 'Access-Control-Allow-Origin' CORS 响应