时间戳

首页 > 解决方案 > 如何在 SQL 中只保留频率最高的行

sql-server - 如何在 SQL 中只保留频率最高的行

问题描述

菜单项

我试图只保留频率最高的行。

例如：

Latte      | 4.2 | 1015
Flat White | 4.2 | 423
Cappuccino | 4.2 | 329

这是我用来获取结果的查询：

SELECT DISTINCT
   "TBL_ITEM"."ItemModName1",
   ("TBL_ITEM"."ItemPrice" / "TBL_ITEM"."Qty") as PricePerUnit,
   Count (*) as Frequency
FROM 
    "dbo"."tbl_item" AS "TBL_ITEM"

WHERE (
   "TBL_ITEM"."ItemPrice" > 0.0
   AND "TBL_ITEM"."UnitPrice" > 0.0
   AND "TBL_ITEM"."Type" = 1
   AND "TBL_ITEM"."CheckDate" BETWEEN '20200421 00:00:00.000' AND '20200427 23:59:59.997'
)
GROUP BY 
   "TBL_ITEM"."ItemModName1",
   ("TBL_ITEM"."ItemPrice" / "TBL_ITEM"."Qty")
ORDER BY   Count (*) DESC

标签： sql-server

解决方案

您可以使用ROW_NUMBER()窗口功能来做到这一点：

SELECT ItemModName1, PricePerUnit, Frequency
FROM (
  SELECT 
    "TBL_ITEM"."ItemModName1",
    ("TBL_ITEM"."ItemPrice" / "TBL_ITEM"."Qty") as PricePerUnit,
    COUNT(*) as Frequency,
    ROW_NUMBER() OVER (PARTITION BY "TBL_ITEM"."ItemModName1" ORDER BY COUNT(*) DESC) as rn
  FROM "dbo"."tbl_item" AS "TBL_ITEM"
  WHERE (
    "TBL_ITEM"."ItemPrice" > 0.0
    AND "TBL_ITEM"."UnitPrice" > 0.0
    AND "TBL_ITEM"."Type" = 1
    AND "TBL_ITEM"."CheckDate" BETWEEN '20200421 00:00:00.000' AND '20200427 23:59:59.997'
  )
  GROUP BY 
    "TBL_ITEM"."ItemModName1",
    ("TBL_ITEM"."ItemPrice" / "TBL_ITEM"."Qty")
) t
WHERE rn = 1
ORDER BY Frequency DESC

您不需要DISTINCT，因为您使用 GROUP BY。

推荐阅读