小组计数订购

发布于 2025-01-18 09:16:14 字数 492 浏览 0 评论 0原文

我的数据如下所示：

group   resource    count
A          X          5
A          Y          8
A          Z          2
B          E          8
B          F          10
B          G          2

我想对数据进行排序，使该组使用 SQL 语句位于具有最高计数总和的顶部。例如：

group   resource    count
B          F          10
B          E          8
B          G          2
A          Y          8
A          X          5
A          Z          2

我也避免使用多个 select 语句。对此有任何帮助。谢谢

原文

I have the data which looks like below:

group   resource    count
A          X          5
A          Y          8
A          Z          2
B          E          8
B          F          10
B          G          2

I want to order the data in a way that the group comes on the top having highest sum of count using SQL statement. Such as:

group   resource    count
B          F          10
B          E          8
B          G          2
A          Y          8
A          X          5
A          Z          2

I am avoiding using multiple select statements too. Any help for this. Thanks

分享到QQ

分享到微博

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

发布评论

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

橘香 2025-01-25 09:16:15

尝试使用窗口函数来总结您的计数和订购。您的DBMS尚未列出，因此可能需要一些小调整来进行语法，但我认为这对大多数DBM都有用

SELECT [Group]
    ,[Resource]
    ,[Count]
    ,TotalCountOfGroup = SUM([Count]) OVER (PARTITION BY [Group])
FROM YourTable
ORDER BY TotalCountOfGroup DESC,[Count] DESC

，或者如果您需要排除TotalCount列，则可以将其包装在CTE中

WITH cte_YourData AS (
    SELECT [Group],[Resource],[Count]
        ,TotalCountOfGroup = SUM([Count]) OVER (PARTITION BY [Group])
    FROM YourTable
)
SELECT [Group],[Resource],[Count]
FROM cte_YourData
ORDER BY TotalCountOfGroup DESC,[Count] DESC

Try using a window function to sum your count and order by that. Your DBMS was not listed, so might need some minor tweaks to syntax, but I think this should work for most DBMS

SELECT [Group]
    ,[Resource]
    ,[Count]
    ,TotalCountOfGroup = SUM([Count]) OVER (PARTITION BY [Group])
FROM YourTable
ORDER BY TotalCountOfGroup DESC,[Count] DESC

Or if you need to exclude the TotalCount column, can wrap it in a CTE

WITH cte_YourData AS (
    SELECT [Group],[Resource],[Count]
        ,TotalCountOfGroup = SUM([Count]) OVER (PARTITION BY [Group])
    FROM YourTable
)
SELECT [Group],[Resource],[Count]
FROM cte_YourData
ORDER BY TotalCountOfGroup DESC,[Count] DESC

回复收藏 0 原文

~没有更多了~