如何在sql中只看到表中最上面的值而不是整个表的COUNT结果?

Question

我正在sssms18中为我的公用事业账单创建一个数据库，并且我试图检索在账单最高的前10个月中某个特定月份出现的计数。如何将COUNT功能限制为只计算最上面的行数?

表设计

CREATE TABLE Electric (
[date] date NOT NULL, 
electric_bill_amount smallmoney);

我可以通过添加WHERE语句来限制结果

SELECT DISTINCT          DATENAME(MONTH, [date]) AS MONTH,
COUNT(DATENAME(MONTH, [date])) AS [Frequency]
FROM Electric
WHERE electric_bill_amount > 104
GROUP BY DATENAME(MONTH, [date])
ORDER BY [Frequency] DESC

，但我希望我的查询更动态，因此只让count语句使用前10个最高值的月份。

结果应该看起来像这样

August 3
September 3
July 2
January 1
December 1

Answer 1

1. 我猜你使用的是SQL Server，而不是MySQL。
2. 只是为了澄清你想要得到前10个最高的电费金额，看看它们是否都落在哪个月。
3. 在dbfiddle上测试

SELECT         DATENAME(MONTH, [date]) AS MONTH,
COUNT(DATENAME(MONTH, [date])) AS [Frequency]
FROM (
SELECT [date], electric_bill_amount, RANK() OVER(ORDER BY electric_bill_amount DESC) as r
FROM Electric
) tmp
WHERE r <= 10
GROUP BY DATENAME(MONTH, [date])
ORDER BY [Frequency] DESC;