mysql - 确定 M :N database system 中未使用的值

Question

这个问题包括一个数据库系统，该数据库系统将组件与其供应商相关联并具有相应的成本，这些供应商用于构建小部件组件。

这是数据库和表:

CREATE DATABASE IF NOT EXISTS DB;

USE DB;

CREATE TABLE components (
    c_id INT AUTO_INCREMENT NOT NULL,
    name_c VARCHAR(255),
    PRIMARY KEY (c_id)
);

CREATE TABLE suppliers (
    s_id INT AUTO_INCREMENT NOT NULL,
    name_s VARCHAR(255),
    PRIMARY KEY (s_id)
);

CREATE TABLE widgets (
    w_id INT AUTO_INCREMENT NOT NULL,
    name_w VARCHAR(255),
    PRIMARY KEY (w_id)
);

CREATE TABLE assemblies (
    w_id INT,
    c_id INT,
    FOREIGN KEY (w_id)
        REFERENCES widgets (w_id),
    FOREIGN KEY (c_id)
        REFERENCES components (c_id)
);

CREATE TABLE prices (
    p_id INT AUTO_INCREMENT NOT NULL,
    s_id INT,
    c_id INT,
    cost INT,
    PRIMARY KEY (p_id),
    FOREIGN KEY (s_id)
        REFERENCES suppliers (s_id),
    FOREIGN KEY (c_id)
        REFERENCES components (c_id)
);

insert into components(name_c)
    values('C1');
insert into components(name_c)
    values('C2');
insert into components(name_c)
    values('C3');
insert into components(name_c)
    values('C4');
insert into components(name_c)
    values('C5');
insert into components(name_c)
    values('C6');

insert into suppliers(name_s)
    values('S1');
insert into suppliers(name_s)
    values('S2');
insert into suppliers(name_s)
    values('S3');
insert into suppliers(name_s)
    values('S4');
insert into suppliers(name_s)
    values('S5');

insert into widgets(name_w)
    values('A1');
insert into widgets(name_w)
    values('A2'); 
insert into widgets(name_w)
    values('A3');  
insert into widgets(name_w)
    values('A4');

insert into assemblies(w_id, c_id)
    values(1,1);
insert into assemblies(w_id, c_id)
    values(2,2);
insert into assemblies(w_id, c_id)
    values(2,3);
insert into assemblies(w_id, c_id)
    values(3,4);
insert into assemblies(w_id, c_id)
    values(3,5);
insert into assemblies(w_id, c_id)
    values(4,6);
insert into assemblies(w_id, c_id)
    values(4,3);

insert into prices(s_id, c_id, cost)
    values(1,1,121);
insert into prices(s_id, c_id, cost)
    values(1,2,135);
insert into prices(s_id, c_id, cost)
    values(2,2,94);    
insert into prices(s_id, c_id, cost)
    values(2,3,155);
insert into prices(s_id, c_id, cost)
    values(3,3,178);
insert into prices(s_id, c_id, cost)
    values(3,4,199);
insert into prices(s_id, c_id, cost)
    values(4,4,122);
insert into prices(s_id, c_id, cost)
    values(4,5,155);
insert into prices(s_id, c_id, cost)
    values(5,5,133);
insert into prices(s_id, c_id, cost)
    values(5,6,184);

生成的表格如下:

组件

c_id | name_c
 1     C1
 2     C2
 3     C3
 4     C4
 5     C5
 6     C6

(注意:表格组件、供应商和小部件均以类似方式定义，例如小部件具有 1-A1、2-A2 等，供应商具有 1-S1、2-S2 等)

价格 - 供应商-组件成本组合列表

p_id s_id c_id cost
1     1     1   121
2     1     2   135
3     2     2   94
4     2     3   155
5     3     3   178
6     3     4   199
7     4     4   122
8     4     5   155
9     5     5   133
10    5     6   184

assemblies - 用于构建小部件程序集的组件列表

w_id | c_id
  1    1  
  2    2  
  2    3 
  3    4  
  3    5  
  4    6  
  4    3  

对于第一部分，在帮助下我能够确定正确的查询以找到关于程序集的最低成本输出，如下所示(目标是确定构建每个小部件程序集的最低成本组件):

select w.name_w, sum(price_min.min_cost) as min_cost
from widgets as w 
left join assemblies as a on a.w_id = w.w_id 
left join (select c_id, min(cost) as min_cost from prices group by c_id) as price_min on price_min.c_id = a.c_id 
group by w.name_w 
order by w.w_id 

输出:

 name_w  min_cost  
 A1     121
 A2     249
 A3     255
 A4     339

但是，我一直在试图弄清楚如何确定哪些供应商 (name_s) 未用于此成本优化(即，没有提供有竞争力的组件价格以用于包含这些组件的相应小部件组装构建供应商的组件)。在纸上进行计算，我希望得到以下结果:

 name_s
 S3

但我似乎无法使用 SQL(特别是 MySQL)获得此结果...

提前致谢!

Answer 1

修改自寻找最低成本的查询:

select distinct s.name_s
from suppliers s
left join
prices p
on s.s_id = p.s_id
where p.s_id not in (
select distinct s_id from prices p1 -- suppliers of low cost components
inner join
(select a.c_id, price_min.min_cost
  from widgets as w 
  left join assemblies as a on a.w_id = w.w_id 
  left join (select c_id, min(cost) as min_cost from prices group by c_id) as price_min 
  on price_min.c_id = a.c_id ) t -- component price details before sum()
on p1.cost = t.min_cost and 
   p1.c_id = t.c_id  )