我正在尝试选择 10 个记录(购买)数量最少的客户。我的表是:产品、客户、购买(id、customer_id、product_id)。我的想法是选择客户,将它们分组,然后编写嵌套查询并获取最小计数,如下所示:
SELECT last_name, name, COUNT(pur.id) FROM customers AS c
INNER JOIN purchases AS pur ON c.id=pur.customer_id
INNER JOIN products AS pr ON pr.id=pur.product_id
GROUP BY last_name, name
HAVING count(pur.id) = (SELECT min(pur.id) FROM
(SELECT count(pur.id) FROM customers AS c
INNER JOIN purchases AS pur ON c.id=pur.customer_id
INNER JOIN products AS pr ON pr.id=pur.product_id
GROUP BY last_name, name))
但我不知道该怎么做?
实际上你很接近。您必须先按人分组并清点钱包。然后按人数排序,选出10个第一人。
问题中缺少一个细节。您说过您想要最少购买数量。在数据库模式中,一笔购买包含许多产品。示例查询计算所有购买中的产品总数。在我的解决方案中,这是相同的。如果您只对购买感兴趣,只需将连接放到产品表中即可。