mysql 根据相似商品的数量返回具有相似购买和 desc 订单的人

发布于 2025-01-16 14:41:04 字数 1002 浏览 4 评论 0原文

客户表

id	名称
1	a
2	b
3	c
4	d

采购表

Product_id	customer_id
x	1
y	1
x	4
y	4
x	3
z	2

客户表包含客户数据，采购表包含订单数据。现在有个问题，我想要购买类似产品的客户 ID，并按类似商品的数量订购例如：如果我想要购买类似商品的客户，例如客户“a” 查询应返回

customer_id	类似商品计数
4	2
3	1

'a' 购买了 x,y d' 买了 x,y, c' 购买了 x，

所以 d 和 c 应该按相似项目计数 (desc) 返回订单，

我不擅长较大的 sql 查询，所以我需要问这个。

先感谢您

原文

customers table

id	name
1	a
2	b
3	c
4	d

purchase table

product_id	customer_id
x	1
y	1
x	4
y	4
x	3
z	2

the customer table has customer data and purchase table has order data.
Now coming to question, I want customers id who bought similar products ordered by the count of similar items
eg:
if i want customers who bought similar items like customer 'a'
the query should return

customer_id	similar items count
4	2
3	1

'a' bought x,y
d' bought x,y,
c' bought x

so d and c should be returned order by similar items count (desc)

i am not good at larger sql queries, so i need to ask this.

Thank you in advance

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

疾风者 2025-01-23 14:41:04

SELECT t1.customer_id, t2.customer_id, COUNT(*) cnt
FROM purchase t1
JOIN purchase t2 ON t1.product_id = t2.product_id
                AND t1.customer_id < t2.customer_id
GROUP BY t1.customer_id, t2.customer_id;

将返回客户对以及为他们提供类似产品的数量。

SELECT t1.customer_id, t2.customer_id, COUNT(*) cnt
FROM purchase t1
JOIN purchase t2 ON t1.product_id = t2.product_id
                AND t1.customer_id < t2.customer_id
GROUP BY t1.customer_id, t2.customer_id;

will return customers pair and the amount of similar products for them.

回复收藏 0 原文

~没有更多了~