mysql - 如何编写一条sql语句来查找具有相同费用表的客户？

Question

假设一家房屋清洁公司有表 Fee_schedule，其中列出了每种类型任务收取的费用。

create table fee_schedule (
  clientId int, 
  feeType int,
  feeAmount int,  /*dollars*/
  primary key (clientId, feeType)
);

费用如下:

Client 1 (Google)
    feeType 1 (Window Cleaning)  $10 (per each window)
    feeType 2 (Carpet Cleaning)  $20 (per room)
    feeType 3 (Emptying trashcans)  $30 (per trash can)
    feeType 4 (Dusting)  $40 (per room)

Client 2 (Facebook)
    feeType 1 (Window Cleaning)  $10 (per each window)
    feeType 2 (Carpet Cleaning)  $20 (per room)
    feeType 3 (Emptying trashcans)  $31 (per trash can)
    feeType 4 (Dusting)  $41 (per room)

Client 3 (Apple)
    feeType 1 (Window Cleaning)  $12 (per each window)
    feeType 2 (Carpet Cleaning)  $22 (per room)
    feeType 3 (Emptying trashcans)  $32 (per trash can)
    feeType 4 (Dusting)  $42 (per room)

Client 4 (Twitter)
    feeType 1 (Window Cleaning)  $10 (per each window)
    feeType 2 (Carpet Cleaning)  $20 (per room)
    feeType 3 (Emptying trashcans)  $30 (per trash can)
    feeType 4 (Dusting)  $40 (per room)

这里是示例数据:

insert into fee_schedule values (1, 1, 10);
insert into fee_schedule values (1, 2, 20);
insert into fee_schedule values (1, 3, 30);
insert into fee_schedule values (1, 4, 40);

insert into fee_schedule values (2, 1, 10);
insert into fee_schedule values (2, 2, 20);
insert into fee_schedule values (2, 3, 31);
insert into fee_schedule values (2, 4, 41);

insert into fee_schedule values (3, 1, 12);
insert into fee_schedule values (3, 2, 22);
insert into fee_schedule values (3, 3, 32);
insert into fee_schedule values (3, 4, 42);

insert into fee_schedule values (4, 1, 10);
insert into fee_schedule values (4, 2, 20);
insert into fee_schedule values (4, 3, 30);
insert into fee_schedule values (4, 4, 40);

我编写了一个查询来获取与客户 #1 Google 具有相同费用表的客户列表。

select distinct f2.clientId
from fee_schedule f
join fee_schedule f2 on f.feeType=f2.feeType and f.feeAmount=f2.feeAmount
where f.clientId<f2.clientId

查询返回客户端 2 和 4，但不应返回客户端 2，因为其费用仅部分相同。如何编辑此查询以仅在所有费用匹配时返回结果？

Answer 1

您可以通过使用 group by 和 having 子句计算匹配数来实现此目的:

select f.clientId
from fee_schedule f left join
     fee_schedule fg
     on f.feeType = fg.feeType and f.feeAmount = fg.feeAmount and fg.clientid = 1
group by f.clientid
having count(*) = (select count(*) from fee_schedule f where f.clientid = 1) and
       count(fg.clientid) = count(*);

编辑:

在 MySQL 中，您还可以使用 group_concat() 来执行此操作:

select f.client_id
from fee_schedule f
group by f.client_id
having group_concat(fee_type, ':', fee_amount order by fee_type, fee_amount) =
       (select group_concat(fee_type, ':', fee_amount order by fee_type, fee_amount) as fees
        from fee_schedule
        where client_id = 1
       );

Here是一个包含两个查询的 SQL Fiddle。