如标题所述,我希望选择前3个值,如果计数的值重复,也包括它。
SELECT b.planID, count(b.planID) AS PopularPlan
FROM dbo.Subscriber as b
GROUP BY B.planID
ORDER BY count(b.planID) DESC;
此输出返回所有最大值,例如:
PlanID PopularPlan
101 2555
123 2555
432 2390
23 2390
45 2090
12 2080
55 2090
如果我使用TOP3,选择TOP3,使用以下SQL:
SELECT TOP 3 b.planID, count(b.planID) AS PopularPlan
FROM dbo.Subscriber as b
GROUP BY B.planID
ORDER BY count(b.planID) DESC;
它将返回以下内容:
PlanID PopularPlan
101 2555
123 2555
432 2390
在这种情况下,想要的输出是做我想做的事情:
PlanID PopularPlan
101 2555
123 2555
432 2390
23 2390
45 2090
我知道TOP3只限于三个结果,但如果重复,我似乎无法找到如何添加的方法。
此外,如果我要包括WITH TIES:
SELECT TOP 3 WITH TIES b.planID, count(b.planID) AS PopularPlan
FROM dbo.Subscriber as b
GROUP BY B.planID
ORDER BY count(b.planID) DESC;
这种情况下的输出是:
PlanID PopularPlan
101 2555
123 2555
432 2390
23 2390
在测试时,如果我选择TOP 5,则会出现2090值,但如果可能的话,我希望对TOP 3也这样做,以便输出如下:
PlanID PopularPlan
101 2555
123 2555
432 2390
23 2390
45 2090 <- has to have this value too
DENSE_RANK((是您的朋友:
WITH cteOriginal AS
(
SELECT b.planID, count(b.planID) AS PopularPlan
FROM dbo.Subscriber as b
GROUP BY B.planID
)
, cteDenseRank AS
(
SELECT *, DENSE_RANK() OVER( ORDER BY PopularPlan DESC) AS DRank
FROM cteOriginal
)
SELECT planID, PopularPlan
FROM cteDenseRank
WHERE DRank <= 3
ORDER BY PopularPlan DESC
;
假设您可以将两个CTE合并在一起,但我永远不确定OVER函数是如何与GROUP BY进行交互的,所以我用了稍微长一点的方法。
就我个人而言,自从ROW_NUMBER((、RANK((和DENSE_RANK((窗口函数出现在T-SQL(2008?(中以来,我几乎再也不用TOP了。
已编辑-DENSE_RANK((显示最高结果。