SQL 查询 - 选择前 5 行并仅在用户存在时进一步选择行

Question

我有点卡在如何实现这个查询上 - 这与我之前发布的查询非常相似，但我无法破解它。

我有一个购物表，每当用户购买任何东西时，都会插入一条记录。

部分字段是

* shopping_id (primary key)
* store_id
* user_id

现在我需要的是只拉出他是前 5 名访客的那些商店的列表:

当我分解它时——这就是我想要完成的:

* Find all stores where this UserA has visited
* For each of these stores - see who the top 5 visitors are.
* Select the store only if UserA is among the top 5 visitors.

相应的查询是:

select store_id from shopping where user_id = xxx

select user_id,count(*) as 'visits' from shopping where store_id in (select store_id from shopping where user_id = xxx)group by user_idorder by visits desclimit 5

现在，如果 UserA 存在，我需要检查此结果集，并且仅当他存在时才选择该商店。例如，如果他访问了一家商店 5 次 - 但如果有 5 人或更多人访问该商店超过 5 次 - 则不应选择该商店。

所以我有点迷路了。

谢谢你的帮助

Answer 1

这应该可以做到。它使用中间 View 来计算每个用户在每个商店购物的次数。此外，它假设您在某处有一个 stores 表，每个 store_id 都列出一次。如果不是这样，您可以将 SELECT store_id FROM stores 更改为 SELECT DISTINCT store_id FROM shopping 以获得相同的效果，但结果较慢。

 CREATE VIEW shop_results (store_id, user_id, purchase_count) AS
     SELECT store_id, user_id, COUNT(*)
     FROM shopping GROUP BY store_id, user_id

 SELECT store_id FROM stores 
    WHERE 'UserA' IN 
      (SELECT user_id FROM shop_results 
       WHERE shop_results.store_id = stores.store_id 
       ORDER BY purchase_count DESC LIMIT 5)

您可以通过将来自 VIEW 的 SELECT 放在子查询中来将这些组合成一个查询，但我认为这样更容易阅读，而且您可能确实希望系统中的其他地方有聚合信息 —在 View 中定义一次比在多个查询中重复定义更一致。