我创建了以下表格:
create table customers (
ID varchar(9),
name varchar(15),
CONSTRAINT pk_id PRIMARY KEY (ID)
);
create table living_places (
code varchar(7),
ID varchar(9),
CONSTRAINT pk_code PRIMARY KEY (code)
);
create table policies (
code_policy varchar(7),
code_living_place varchar(7),
CONSTRAINT pk_code_policy PRIMARY KEY (code_policy)
);
create table bills (
code varchar(7),
code_policy varchar(7),
paid_out boolean,
CONSTRAINT pk_code_bill PRIMARY KEY (code)
);
我插入了以下日期:
insert into customers(ID, name) values('fx1','Louis');
insert into customers(ID, name) values('fx2','Peter');
insert into customers(ID, name) values('fx3','Alice');
insert into living_places(code, ID) values('001','fx1');
insert into living_places(code, ID) values('002','fx2');
insert into living_places(code, ID) values('003','fx1');
insert into living_places(code, ID) values('004','fx3');
insert into policies(code_policy, code_living_place) values('p1','001');
insert into policies(code_policy, code_living_place) values('p2','002');
insert into policies(code_policy, code_living_place) values('p3','003');
insert into bills(code, code_policy, paid_out) values('b1','p1','1');
insert into bills(code, code_policy, paid_out) values('b2','p1','1');
insert into bills(code, code_policy, paid_out) values('b3','p2','0');
insert into bills(code, code_policy, paid_out) values('b4','p2','1');
insert into bills(code, code_policy, paid_out) values('b5','p3','0');
insert into bills(code, code_policy, paid_out) values('b6','p3','1');
问题是:如何选择那些已支付所有保单的人?
我的问题是Louis
有两项政策p1
and p3
。政策p1
已付款但p3
is not.
我的查询:
select ID from living_places where code in (
select code from living_places where code in (
select code_living_place from policies where code_policy in (
select code_policy from bills where paid_out=1 and code_policy not in (
select code_policy from bills where paid_out=0))));
MySQL 返回给我:
+------+
| ID |
+------+
| fx1 |
+------+
P.S: Louis
没有支付所有保单。例如,账单b5
未付款。