mysql - SUM 列的最小值和最大值

Question

我有一张像这样的桌子：

Phrase    |  qty
phrase_1  |  4  
phrase_1  |  1
phrase_1  |  8
phrase_2  |  2
phrase_3  |  3
phrase_3  |  2

我最初返回的是：

phrase_1 | 13
phrase_3 | 5
phrase_2 | 2

使用：

SELECT phrase, sum(qty) as total
FROM mytable
GROUP By phrase
ORDER BY total DESC

我需要并且无法弄清楚的是如何返回结果的最小值和最大值。

所以我会得到：

phrase, qty, min, max
phrase_1 | 13 | 2 | 13
phrase_3 | 5  | 2 | 13
phrase_2 | 2  | 2 | 13

因为我想对结果集进行规范化并根据 1 到 0 之间的值返回一个新顺序。

类似的东西（这不起作用）：

SELECT phrase, sum(qty) as total, (total - min(total)/max(total) - min(total)) AS rank
FROM mytable
GROUP By phrase
ORDER BY rank DESC

上述声明最终是我想要做的，但不确定是否可能。

标签： mysqlsql

Answer 1

你想要窗口功能：

SELECT phrase, sum(qty) as total,
       MIN(SUM(qty)) OVER () as min_total,
       MAX(SUM(qty)) OVER () as max_total
FROM mytable
GROUP By phrase
ORDER BY total DESC