SQL Server：查找每个用户购买的最常用的产品类别，以便在子查询中使用-Java 学习之路

我有三个表： categories （id，name）， products （id，category_id，name））和 purchases （id，user_id，product_id） . product 属于 category . 用户可以购买许多 products . 我的目的是找到每个用户最热门的 category .

但是，我需要使用查询的结果集作为子查询，因此由于SQL Server限制（可怕的 The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified. 错误），遗憾的是使用任何 ORDER BY 语句都是关闭的 .

我的方法是每个 category 创建一个每个用户 purchases 的列表 . 然后我有一个 MAX 函数来挑选 purchases 的最大数量 . 我将该结果连接到原始查询（作为子查询复制）以检索有问题的 category_id ，最后我获取类别名称 .

我的查询有两个问题：

显然我不希望在我的代码中两次使用相同的查询 . 但是，我不能依赖于使用CTE或临时表，因为此查询的结果旨在链接到具有用户数据子集的视图，并且VIEW代码旨在用于第三个-party包，只能处理基本的SQL代码 .
如果出现平局（比如一个使用者买了4个 products ，每个2个 categories 就有2个），我最终得到了该用户的重复行 .

小提琴：

http://sqlfiddle.com/#!6/8821b/5

如果有人能帮我找出确保每个用户只返回一行的方法，以及删除重复子查询的方法，我将不胜感激 .

谢谢！

1 回答

首先，感谢您在SQLFiddle中提供示例 . 它使ALOT更容易帮助 .

您可以使用row_number获得更精确的获取“顶级”记录的方法 . 在此示例中，我选择在计数后使用category_name作为辅助排序条件 .

SELECT user_id, category_name, category_count
FROM
(
  SELECT 
      user_id, COUNT(1) as category_count, category_name, 
      ROW_NUMBER() OVER (
          PARTITION BY user_id 
          ORDER BY COUNT(1) DESC, category_name ASC) 
          as ordinal_position
  FROM
      purchases p 
          JOIN products p2 ON p.product_id = p2.id
          JOIN categories c ON p2.category_id = c.id        
  GROUP BY user_id, category_name
 ) a
WHERE ordinal_position = 1
ORDER BY category_count DESC

Example at SQL Fiddle.

回复于 2024-04-26T06:58:16+08:00

SQL Server：查找每个用户购买的最常用的产品类别，以便在子查询中使用

1 回答

相关问题