sql - 仅限 Oracle SQL:Case 语句或存在查询以根据条件显示结果
问题描述
我正在尝试使用 case 语句根据某些条件创建计算列。我离目标很近,但看不到查询出错的地方。希望我能在这里得到一些最好/更简单的方法和一些帮助。
以下是表格:
PERSON 表格:
+----+--------+
| ID | PERSON |
+----+--------+
| 1 | John |
| 2 | Scott |
| 3 | Ruth |
| 4 | Smith |
| 5 | Frank |
| 6 | Martin |
| 7 | Blake |
+----+--------+
角色表:
+----+------+
| ID | ROLE |
+----+------+
| 1 | JJJ |
| 2 | Auth |
| 3 | AAA |
| 4 | MMM |
| 5 | KKK |
| 6 | BBB |
+----+------+
最后一个是
明细表 PERSON_ROLE 表:
+----+-----------+---------+
| ID | PERSON_ID | ROLE_ID |
+----+-----------+---------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 2 | 3 |
| 4 | 2 | 4 |
| 5 | 3 | 1 |
| 6 | 3 | 5 |
| 7 | 4 | 3 |
| 8 | 5 | 6 |
| 9 | 6 | 3 |
| 10 | 6 | 6 |
| 11 | 6 | 2 |
| 12 | 7 | 5 |
| 13 | 7 | 6 |
+----+-----------+---------+
期望/预期输出:
+--------+--------+----------+
| PERSON | MYROLE | MYACCESS |
+--------+--------+----------+
| John | JJJ | |
| Scott | Auth | Remove |
| Scott | AAA | |
| Scott | MMM | |
| Ruth | JJJ | |
| Ruth | KKK | |
| Smith | AAA | Add |
| Frank | BBB | Add |
| Martin | AAA | |
| Martin | BBB | |
| Martin | Auth | Remove |
| Blake | KKK | |
| Blake | BBB | Add |
+--------+--------+----------+
以下是条件:
- 如果Person具有角色“AAA”或“BBB”而不是“Auth”,则MYAccess列应将该人的值显示为“Add”。所有其他值都应为空。
- 如果Person具有角色“Auth”,则MYAccess列应仅针对该行显示“Remove”。即使同一个人具有“AAA”或“BBB”或任何其他值,它也应该显示为 null。
以下是我得到的部分正确的实际输出:
+--------+--------+----------+
| PERSON | MYROLE | MYACCESS |
+--------+--------+----------+
| Blake | KKK | |
| Blake | BBB | Add |
| Frank | BBB | Add |
| John | JJJ | |
| Martin | AUTH | Remove |
| Martin | BBB | Add |
| Martin | AAA | Add |
| Ruth | JJJ | |
| Ruth | KKK | |
| Scott | AAA | Add |
| Scott | AUTH | Remove |
| Scott | MMM | |
| Smith | AAA | Add |
+--------+--------+----------+
对于 Persons Martin 和 Scott,它应该只显示“删除”,但我也得到了“添加”。
以下是查询:
SELECT p.person,upper(r.role) myrole,
case when p.person in (select p.person from person ut where ut.person = p.person and upper(r.role) = upper('Auth')) then 'Remove'
when p.person in (select p.person from person ut where ut.person = p.person and (upper(r.role) = upper('Auth') or (upper(r.role) = upper('AAA') or upper(r.role) = upper('BBB')))) then 'Add' else null
end as myaccess
FROM person p
join person_role pr
ON p.id = pr.person_id
join myrole r
ON r.id = pr.role_id
order by p.person
DDL 脚本:
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 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_role (
id INTEGER NOT NULL,
person_id INTEGER,
myrole_id INTEGER
);
ALTER TABLE person_role ADD CONSTRAINT person_role_pk PRIMARY KEY ( id );
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;
/
谢谢
里查
解决方案
这是你要找的吗?
在您的条件下,您是说:
“如果 Person 具有角色“AAA”或“BBB”而不是“Auth”,那么 MYAccess 列应该将该人的值显示为“Add”。所有其他值都应该为 null。“
Scott 有 Auth所以这个条件产生错误。根据这条规则,斯科特不应该有“添加”。在您预期的输出中,它具有“添加”。我错过了什么吗?
WITH person_roles (id, person_id, role_id) AS
(
SELECT 1, 1,1 FROM DUAL UNION ALL
SELECT 2, 2,2 FROM DUAL UNION ALL
SELECT 3, 2,3 FROM DUAL UNION ALL
SELECT 4, 2,4 FROM DUAL UNION ALL
SELECT 5, 3,1 FROM DUAL UNION ALL
SELECT 6, 3,5 FROM DUAL UNION ALL
SELECT 7, 4,3 FROM DUAL UNION ALL
SELECT 8, 5,6 FROM DUAL UNION ALL
SELECT 9, 6,3 FROM DUAL UNION ALL
SELECT 10, 6,6 FROM DUAL UNION ALL
SELECT 11, 6,2 FROM DUAL UNION ALL
SELECT 12, 7,5 FROM DUAL UNION ALL
SELECT 13, 7,6 FROM DUAL
),persons (id, person) AS
(
SELECT 1,'John' FROM DUAL UNION ALL
SELECT 2,'Scott' FROM DUAL UNION ALL
SELECT 3,'Ruth' FROM DUAL UNION ALL
SELECT 4,'Smith' FROM DUAL UNION ALL
SELECT 5,'Frank' FROM DUAL UNION ALL
SELECT 6,'Martin' FROM DUAL UNION ALL
SELECT 7,'Blake' FROM DUAL
),roles (id, role) AS
(
SELECT 1,'JJJ' FROM DUAL UNION ALL
SELECT 2,'Auth' FROM DUAL UNION ALL
SELECT 3,'AAA' FROM DUAL UNION ALL
SELECT 4,'MMM' FROM DUAL UNION ALL
SELECT 5,'KKK' FROM DUAL UNION ALL
SELECT 6,'BBB' FROM DUAL
), rule_1(person_id, role_type) AS
(
SELECT p.id, MIN(CASE WHEN r.ROLE = 'Auth' THEN 0 WHEN r.ROLE in ('AAA','BBB') THEN 1 ELSE 2 END)
FROM person_roles pr
JOIN persons p ON p.id = pr.person_id
JOIN roles r ON r.id = pr.role_id
GROUP BY p.id
)
SELECT 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 as action
FROM person_roles pr
JOIN persons p ON p.id = pr.person_id
JOIN roles r ON r.id = pr.role_id
JOIN rule_1 rl ON rl.person_id = pr.person_id
ORDER BY p.person
PERSON ROLE ACTION
------ ---- ------
Blake BBB Add
Blake KKK
Frank BBB Add
John JJJ
Martin AAA
Martin Auth Remove
Martin BBB
Ruth KKK
Ruth JJJ
Scott MMM
Scott AAA
Scott Auth Remove
Smith AAA Add
如果您想避免 CTE(with 子句),您可以将最后 2 个语句替换为 1:
SELECT 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 as action
FROM person_roles pr
JOIN persons p ON p.id = pr.person_id
JOIN roles 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_roles pr
JOIN persons p ON p.id = pr.person_id
JOIN roles r ON r.id = pr.role_id
GROUP BY p.id
) rl ON rl.id = pr.person_id
ORDER BY p.person
推荐阅读
- python - (2003, "Can't connect to MySQL server on "0.0.0.0" (tiemd out) ) 错误
- api - Flutter Stateless Class 和 Statefull 类坚持使用
- c# - 字典的清晰实例化和新实例化之间的区别
- android - 我在拦截器和改造方面做错了什么?
- python - 在 Python 中读取包含多个对象的 JSON 文件
- arrays - C计算三个数组的每次组合乘法之和时超时
- github - 为我正在观看的所有 GitHub 存储库设置自定义通知规则
- php - 使用 WHERE IN 查询以分号分隔的值列
- android - 我的应用程序可以知道使用哪个二维码下载它吗
- kubernetes - Kubernetes 跨 Pod 共享文件系统