按组排序，并显示单个条目

I have following scenario where i want to order the table and arrange by groups having highest to lowest sum

我有以下场景，我想要对表进行排序，并按拥有最高和最低和的组进行排序

 name   score
----------------
  abc    10
  pqr    9
  abc    7
  pqr    3
  abc    10
  xyz    7
  pqr    7
  xyz    3
  xyz    2

now if we observe,

如果我们观察,

total (abc) = 10 + 7 + 10 = 27

total (pqr) = 9 + 3 + 7 = 19

总(pqr) = 9 + 3 + 7 = 19。

total (xyz) = 7 + 3 + 2 = 12

How to sort the above table in SQL by group with highest sum and it should display individual entries?

如何在SQL中对上面的表进行排序，并且应该显示单个条目?

Expected output:
----------------
name   score 
----------------
abc    10
abc    10
abc    7
pqr    9
pqr    7
pqr    3
xyz    7
xyz    3
xyz    2

2 个解决方案

#1

SQLite doesn't have analytical/windowed functions, so you need to work out the different pieces of data yourself.

SQLite没有分析/窗口函数，所以您需要自己处理不同的数据。

SELECT
  yourTable.*
FROM
  yourTable
INNER JOIN
(
  SELECT name, SUM(score) AS score FROM yourTable GROUP BY name
)
  AS totalScores
    ON totalScores.name = yourTable.name
ORDER BY
  totalScores.score DESC,
  yourTable.name,
  yourTable.score   DESC

In this query, there is a sub-query. The sub-query calculates the totalScore for each name.

在这个查询中，有一个子查询。子查询计算每个名称的totalScore。

This then lets you put that total score in the ORDER BY clause. Note, I don't select the total score in my results, you can but it's not necessary.

这样就可以将总分放在ORDER BY子句中。注意，我不会在我的结果中选择总分，你可以，但是没有必要。

Also, I've put the name in the ORDER BY. In this way, if there is a tie with more than one name sharing the same total score, the name that is first alphabetically will be shown first.

而且，我把名字按顺序排了出来。这样，如果有一个与多个名字共享相同总分的领带，第一个字母的名字将首先显示。

#2

If you use SQL-Server, one way is using SUM(score)OVER(PARTITION BY name)

如果使用sql server，一种方法是使用SUM(分数)除以(按名称划分)

SELECT name,
       score,SUM(score)OVER(PARTITION BY name)
FROM dbo.TableName
ORDER BY SUM(score)OVER(PARTITION BY name) DESC,
         score DESC

DEMO

演示

OVER Clause (Transact-SQL)

在条款(transact - sql)

#1