我如何看到表中最高值的计数结果，而不是SQL中的整个表

发布于 2025-01-26 10:55:07 字数 676 浏览 2 评论 0原文

我正在为SSMS18中的公用事业账单创建数据库，并且我正在尝试检索最高账单前10个月中特定月份的计数。如何将计数功能限制为仅计数排的最高数？

表设计

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

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

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

10个最高值。

结果应该看起来像这样

August 3
September 3
July 2
January 1
December 1

原文

I am creating a Database for my utility bills in SSMS18, and I am trying to retrieve the count for the occurrence of a specific month in the top 10 months with the highest bills. How do I restrict the COUNT feature to only count the top number of rows?

Table design

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

I can limit the results by adding a WHERE statement

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

but I would like to my query to be more dynamic and therefore only have the count statement use the months with the top 10 highest values.

Results should look something like this

August 3
September 3
July 2
January 1
December 1

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

娇女薄笑 2025-02-02 10:55:07

我想您正在使用SQL Server，而不是MySQL。
只是为了澄清您想获得最高的10个最高电费金额，看看其中的每一个是否属于哪个月。
在

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;

I guess you are using SQL Server, not MySQL.
Just to clarify that you want to get the top 10 highest electric bill amount and see if each of these falls on which month.
Tested on 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;

回复收藏 0 原文

~没有更多了~