如何选择特定客户?

问题描述

我创建以下表格:

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和p3.保单p1已付款，但p3未付款.

My problem is that Louis has two policies p1 and p3. Policy p1 is paid but 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没有支付.

P.S: Louis dont have all the policies paid. For example, the bill b5 is not paid.

推荐答案

我认为应该是这样的:

select name 
from customers
where id not in ( 
    select iving_places.id 
    from living_places
    inner join policies on policies.code_living_place = living_places.code
    inner join bills on ( bills.code_policy = policies.code and bills.paid_out=0)
);

这篇关于如何选择特定客户?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！