我有三個表，想用 SQL 回答以下問題：
“誰只有沒有分數的認證？”
例如，在下面的設定中，查詢將只回傳“John”。Joana 擁有“AWS 認證”，位于 SCORE 表（id?? 57）中。Marry 擁有 SCORE 表中沒有的“ITIL V3 認證”，但她還擁有 SCORE 表中的“Professional Python Dev”。
PERSON
| PERSON_ID | PERSON_NAME |
| -------- | -------------- |
| 12 | John |
| 23 | Mary |
| 24 | Joana |
**CERTIFICATION**
|CERTIFICATION_ID| CERTIFICATION_NAME | PERSON_ID|
| -------- |----------- | -------------- |
| 53 | Java Certification | 12 |
| 54 | ITIL V3 Certification | 23 |
| 55 | Professional Python Dev | 23|
| 56 |GCP Certification |23 |
| 57 |AWS Certification |24 |
SCORES
|SCORE_ID| CERFITICATION_ID | SCORE_DETAILS |
| -------- |----------- | -------------- |
| 70 |55 | 80% |
| 71 |56 | 90% |
| 72 |57 | 95% |
我試圖僅在 SQL 中實作這一點，而不必遍歷記錄并且不使用存盤程序。
SQL 用于創建這些表并添加資料以防萬一有人需要它：
create table person(
person_id integer,
person_name varchar(16)
);
create table certification(
CERTIFICATION_ID int,
CERTIFICATION_NAME varchar(16),
person_id int
);
create table scores(
SCORE_ID int,
CERTIFICATION_ID int,
SCORE_DETAILS varchar(16));
insert into person(person_id, person_name) values(12, 'John');
insert into person(person_id, person_name) values(23, 'Mary');
insert into person(person_id, person_name) values(24, 'Joana');
insert into certification(CERTIFICATION_ID,CERTIFICATION_NAME,person_id) values(53,'A', 12);
insert into certification(CERTIFICATION_ID,CERTIFICATION_NAME,person_id) values(54,'B',23);
insert into certification(CERTIFICATION_ID,CERTIFICATION_NAME,person_id) values(55,'C', 23);
insert into certification(CERTIFICATION_ID,CERTIFICATION_NAME,person_id) values(56,'D', 23);
insert into certification(CERTIFICATION_ID,CERTIFICATION_NAME,person_id) values(57,'E', 24);
insert into scores (SCORE_ID,CERTIFICATION_ID, SCORE_DETAILS) values (70,55,'e');
insert into scores (SCORE_ID,CERTIFICATION_ID, SCORE_DETAILS) values (71,56,'f');
insert into scores (SCORE_ID,CERTIFICATION_ID, SCORE_DETAILS) values (72,57,'g');
uj5u.com熱心網友回復：
您可以使用以下查詢
select *
from person p
where not exists (select 1
from certification c
join scores s on s.certification_id = c.certification_id
where p.person_id = c.person_id
)
uj5u.com熱心網友回復：
我們可以在下面使用 not
select p.* from person p
where p.person_id not in (
select c.person_id from
certification c join scores s on c.CERTIFICATION_ID=s.CERTIFICATION_ID
)
演示鏈接
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/424991.html