sql - 我怎样才能在 oracle 中获得相同案例的人?
问题描述
我有一个名为CASE_PERSON
.DDL 的表是
CREATE TABLE CASE_PERSON
(
ID VARCHAR2(50) NOT NULL PRIMARY KEY,
p_id VARCHAR2(50) DEFAULT NULL NOT NULL,
id_card VARCHAR2(18) DEFAULT NULL NOT NULL,
p_name VARCHAR2(50) NOT NULL,
case_no VARCHAR2(60) NOT NULL,
case_name VARCHAR2(200) NOT NULL,
)
还有一些 DML
insert into CASE_PERSON(ID, P_ID, ID_CARD, P_NAME, CASE_NO, CASE_NAME)
values ('001', '123', '123-123123', 'tom', 'c-001', 'case-one');
insert into CASE_PERSON(ID, P_ID, ID_CARD, P_NAME, CASE_NO, CASE_NAME)
values ('002', '234', '123-123234', 'rose', 'c-001', 'case-one');
insert into CASE_PERSON(ID, P_ID, ID_CARD, P_NAME, CASE_NO, CASE_NAME)
values ('003', '345', '123-123345', 'jack', 'c-001', 'case-one');
insert into CASE_PERSON(ID, P_ID, ID_CARD, P_NAME, CASE_NO, CASE_NAME)
values ('005', '555', '123-123555', 'roman', 'c-002', 'case-two');
insert into CASE_PERSON(ID, P_ID, ID_CARD, P_NAME, CASE_NO, CASE_NAME)
values ('006', '666', '123-123666', 'jim', 'c-002', 'case-two');
该表由Person
表和case
表构建而成。p 的前缀是 fromPERSON
和 case 是 from CASE
。首先,我从中获取所有的身份证件PERSON
并查询 fromCASE_PERSON
以找到与给定身份证件匹配的大小写相同的人。
现在我想得到名为“具有相同情况的人”的结果。输出应为以下格式:
id p_id id_card case_no case_name same_case_id_card same_case_p_name
1 123 123-123123 c-001 case-one 123-123234 rose
2 123 123-123123 c-001 case-one 123-123345 jack
3 555 123-123555 c-002 case-two 123-123666 jim
我该如何进行查询?
解决方案
您似乎想要第一人称的案例详细信息,并与非第一人称的个人详细信息交叉:
WITH cte AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY case_no ORDER BY p_id) as rown FROM case_person
)
SELECT
ROW_NUMBER() OVER(ORDER BY two.p_id) as id,
one.p_id,
one.id_card,
one.case_no,
one.case_name,
two.id_card as same_case_id_card,
two.case_name as same_case_p_name
FROM
(SELECT * FROM cte WHERE rown = 1) one
INNER JOIN
(SELECT * FROM cte WHERE rown > 1) two
ON one.case_no = two.case_no