mysql - 如何在MYSQL中获得身份证？

我创建了以下表格：

create table people (
        ID              varchar(9),
        name            varchar(20),
        CONSTRAINT pk_ID PRIMARY KEY (ID)
);

 create table cars (
        license_plate   varchar(9),
        ID              varchar(9),
        CONSTRAINT pk_ID PRIMARY KEY (license_plate)
);

create table accidents (
        code          varchar(9),
        license_plate varchar(9),
        CONSTRAINT pk_ID PRIMARY KEY (code)
);

我插入了以下数据：

insert into people(ID, name) values('0x1','Louis');
insert into people(ID, name) values('0x2','Alice');
insert into people(ID, name) values('0x3','Peter');

insert into cars(license_plate, ID) values('001','0x1');
insert into cars(license_plate, ID) values('002','0x2');
insert into cars(license_plate, ID) values('003','0x1');
insert into cars(license_plate, ID) values('004','0x3');

insert into accidents(code, license_plate) values('fd1','001');
insert into accidents(code, license_plate) values('fd2','004');
insert into accidents(code, license_plate) values('fd3','002');

问题是：如何选择那些没有在车上发生过事故的人？
我的问题是当我试图使用not in时。如果表中至少有一辆车是“Louis”，则查询将显示“Louis”，而不应显示“Louis”。
我的问题：

select ID from people where ID in (select ID from cars where license_plate not in (select license_plate from accidents));

结果：

+-----+
| ID  |
+-----+
| 0x1 |
+-----+

最佳答案

select name from people where ID not in (
  select distinct c.ID from
    accidents as a inner join cars as c
    on a.license_plate = c.license_plate
)

Explanation=子查询将连接汽车和事故，将提供所有发生事故的汽车的ID。在这里，您可以在people表上运行not in查询