如何写非相关查询与内部连接在sql这个问题?

Question

在国家的基础上，我想要有多个供应商的供应商名单，并且从这些供应商中，我想要那些产品(可以是任何一个)价格低于20的供应商。

供应商表:

supplier_name  id  country
A               1   germany
B               2   london 
C               3   london 
D               4   germany

Products表:

products  price  id
onion      30    1
tomato     20    1
potato     3     1
pulses     60    2
rice       18    2
spice     100    2
jacket    300    3
fruits     8     4

这个问题的sql查询:

select id, 
supplier_name, 
count(country) as sd , 
( select distinct  s.id
from suppliers s 
inner join products p on s.id=p.id 
where price<20) as d 
from suppliers
group by country
having sd > 1;

这是我的查询但是d列和sd列被打印为额外的列，我不需要

谁能帮我写更好的非相关查询与内连接?

我想要一个国家有多个供应商的供应商列表我希望这些供应商的产品价格(可以是任何一种)低于20美元。

看上面的供应商表，德国有两个供应商A, D，这不止一个，从这些供应商中，我想要那些产品(可以是任何一个)价格低于20的。

那么输出将是:

suppliers_name     id    
A               1    
B               2     
D               4     

Answer 1

您的查询在访问中惨败，这只是我所拥有的查询引擎。考虑:

SELECT id, supplier_name 
FROM Suppliers INNER JOIN (
SELECT country, Count(ID) AS CntID
FROM Suppliers GROUP BY country) AS C
ON Suppliers.country = c.country
WHERE CntID>1 AND id IN (SELECT supID FROM Products WHERE price<20)
ORDER BY ID;

或

SELECT DISTINCT Suppliers.id, Suppliers.supplier_name
FROM Products 
INNER JOIN (Suppliers INNER JOIN (SELECT country, Count(ID) AS CntID
FROM Suppliers GROUP BY country) AS C 
ON Suppliers.country = C.country) 
ON Products.supID = Suppliers.ID
WHERE C.[CntID]>1 AND Products.price<20;

我将product中的id重命名为supID。