如何在SQL中为每行记录分配同价格分组的count统计值

需求说明 #

现有Product表存储产品信息，需要统计每个价格对应的产品总数量，并把该统计值挂载到同价格的每一条产品记录上。

• 表字段：Name（产品名）、Price（产品价格）
• 样例原始数据：
  • A：价格12
  • B：价格7
  • C：价格1
  • D：价格1
  • E：价格7
  • F：价格1
• 预期返回字段：Name、Count，其中Count是当前产品所属同价格分组的总产品数，预期结果：
  • A对应Count=1
  • B对应Count=2
  • C对应Count=3
  • D对应Count=3
  • E对应Count=2
  • F对应Count=3

最优解法（支持窗口函数的数据库：MySQL8+、PostgreSQL、SQL Server、Oracle等） #

直接使用COUNT()窗口函数，按Price分区统计即可，不需要额外关联子查询，执行效率最高，写法最简洁：

SELECT
  Name,
  COUNT(*) OVER (PARTITION BY Price) AS Count
FROM Product;

兼容写法（不支持窗口函数的老版本数据库，如MySQL 5.x） #

先按价格分组统计每个价格的产品总数，再通过价格关联回原表，拿到每条记录对应的分组统计值：

SELECT
  p.Name,
  g.total AS Count
FROM Product p
INNER JOIN (
  SELECT
    Price,
    COUNT(*) AS total
  FROM Product
  GROUP BY Price
) g ON p.Price = g.Price;

两种写法返回结果完全匹配预期，数据库版本支持窗口函数的场景下优先选第一种写法。

内容的提问来源于stack exchange，提问作者dg_m87