简单的SQL选择相同列的和和值

I have a co-worker who is working on a table with an 'amount' column. They would like to get the top 5 amounts and the sum of the amounts in the same query.

我有一个同事在一个有“数量”列的桌子上工作。他们希望在同一查询中获得前5个金额和金额总和。

I know you could do this:

我知道你可以这样做:

SELECT TOP 5 amount FROM table 
UNION SELECT SUM(amount) FROM table
ORDER BY amount DESC

But this produces results like this:

但这会产生如下结果:

1000  (sum)
 100
  70
  50
  30
  20

When what they really need is this:

他们真正需要的是这个:

My intuitive attempts to achieve this tend to run into grouping problems, which isn't such an issue when you are selecting a different column, but is when you want to use an aggregate function based on the column you are selecting.

我实现此目的的直观尝试往往会遇到分组问题,当您选择不同的列时,这不是一个问题,但是当您想要根据您选择的列使用聚合函数时。

4 个解决方案

#1

You can use a CROSS JOIN for this:

您可以使用CROSS JOIN:

SELECT TOP 5 a.amount, b.sum
FROM table a
CROSS JOIN (SELECT SUM(amount) sum FROM table) b
ORDER BY amount DESC

#2

This might work

这可能会奏效

SELECT TOP 5 amount, (SELECT SUM(amount) FROM table) 
FROM table 
ORDER BY amount DESC

#3

Not really pretty, but this shouls do it:

不是很漂亮,但是这样做会让人感到惊讶:

SELECT TOP 5 amount,  SAmount
FROM table Join 
  (SELECT SUM(amount) As SAmount FROM table)
ORDER BY amount DESC

As said by others, I'd probably use two queries.

正如其他人所说,我可能会使用两个查询。

#4

Another approach using analytic functions (SQL Server 2005+):

另一种使用分析函数的方法(SQL Server 2005+):

SELECT  TOP 5 amount, SUM(amount) OVER()
FROM    table
ORDER BY
        amount DESC

#1