使用两列作为键的两个表的SQL查询
使用两列作为键的两个表的SQL查询
提问于 2020-06-18 16:51:25
我开始研究SQL (而不是MySQL，因为在某些地方它似乎不一样)，我遇到了一个我无法解决的问题。
我有以下表格：
CREATE TABLE products(
product char(30),
color char(30)
);
INSERT INTO products (product, color)
VALUES
("table","brown"),
("chair","brown"),
("shelf","brown"),
("table","black"),
("chair","white");
CREATE TABLE suppliers(
supplier char(30),
product char(30),
color char(30)
);
INSERT INTO suppliers (supplier, product, color)
VALUES
("s1","chair","brown"),
("s1","door","brown"),
("s1","table","brown"),
("s1","table","black"),
("s1","shelf","brown"),
("s2","chair","brown"),
("s3","table","brown"),
("s3","table","black"),
("s3","chair","brown"),
("s3","chair","white"),
("s3","shelf","white");我需要使用SQL找到供应商和他们可以提供给商店和的棕色产品的数量，商店将出售给他们，按计数的顺序(产品表中的棕色产品)。我从子查询开始，如下所示：
select supplier,COUNT(pb.product)
from (select product from products where color = "brown") as pb, (select supplier, product from suppliers where color = "brown") as sb
where pb.product == sb.product;但我想不出来，请帮帮忙。
这些表格的结果必须是：
supplier| no_of_products
-------------------------
s2 | 1
s3 | 2
s1 | 3回答 3
Stack Overflow用户
回答已采纳
发布于 2020-06-19 00:44:30
最后，我需要以下几点：
select supplier, COUNT(s.product)
From suppliers s, products p
WHERE s.product = p.product and s.color = "brown" and p.color = "brown"
GROUP by supplier
order by COUNT(s.product);Stack Overflow用户
发布于 2020-06-18 16:55:12
看起来您需要简单的聚合：
select supplier, count(*) no_brown_products
from suppliers
where color = 'brown'
group by supplierStack Overflow用户
发布于 2020-06-18 16:59:43
这里有一个联接查询，希望它能解决您的问题。
SELECT supplier,COUNT(pb.product)
FROM product pb
INNER JOIN supliers sp ON sp.color = pb.color
WHERE color = 'brown'
GROUP BY supplier;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/62455234
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号