mysql - MySQL按查询分组并附加行

我有一张像下面这样的桌子，叫做trades

traders   qty

abc      50000
xyz      35000
pqr      25000
xxx      10000
yyy      20000
uuu      15000
abc      2000
xyz      3000
pqr      1500

我需要让前三名的数量明智的交易者和所有其他在一个单独的行。

SELECT trader,SUM(qty)
FROM traders
GROUP BY trader DESC
LIMIT 3

从上面的问题我可以得到如下的答案

tr        qty

abc      52000
xyz      38000
pqr      26500

但我的要求如下

tr        qty

abc      52000
xyz      38000
pqr      26500
other    45000   ----- with this column for all other traders except the largest 3

最佳答案

尝试此查询-

SELECT
  IF(t2.trader IS NULL, 'other', t1.trader) trader,
  SUM(qty) qty
FROM traders t1
  LEFT JOIN (
  SELECT trader, SUM(qty) FROM traders
    GROUP BY trader
    ORDER BY SUM(qty) DESC
    LIMIT 3) t2
  ON t1.trader = t2.trader
GROUP BY trader
ORDER BY IF(t2.trader IS NULL, 1, 0), qty DESC

+--------+-------+
| trader | qty   |
+--------+-------+
| abc    | 52000 |
| xyz    | 38000 |
| pqr    | 26500 |
| other  | 45000 |
+--------+-------+