mysql - 大多数产品如何获得用户

Question

我有 3 个表:

发票:

invoice_id | user_name | user_id | cost | shop_Id 

发票产品:

id | invoice_id | user_id | product_id | quantity

产品:

product_id | product_name | description

我需要 shop_id = 1 结果且客户有多种产品的前 5 位客户:

invoice_id | user_name | user_id | product_name | product_description

Answer 1

您可以按照以下步骤操作 - 加入表格、计算产品数量、按数量排序并使用 LIMIT :

SELECT i.user_name,count(p.product_id) as cnt_products 
FROM invoice i
JOIN invoice_products ip
 ON(i.invoice_id = ip.invoice_id)
JOIN product p
 ON(p.product_id = ip.product_id)
WHERE i.shop_id = 1
GROUP BY i.user_name 
ORDER BY cnt_products DESC
LIMIT 5