MYSql 前 10 名及其他合计 [英] MYSql Top 10 and Others Total

问题描述

我的这个查询运行良好，但我只需要前 10 名供应商.

I have this query that works fine but I need just the top 10 vendors.

然后我需要在所有其他"行中计算所有剩余的总数.

Then I need all the remaining totaled in a "All Others" row.

如何在没有使用 LIMIT 10, 18446744073709551615

SELECT VENDOR_fullname,SUM(POTENTIAL_RECOVERY)
FROM COMPLETE
GROUP BY VENDOR_fullname
ORDER BY SUM(POTENTIAL_RECOVERY) DESC;

推荐答案

为了记录，我同意@DamienBlack 的评论，但是如果它必须在一个查询中完成，UNIONALL 都可以做到:

For the record, I agree with @DamienBlack's comment, however if it had to be done in one query a UNION ALL could do the trick:

(
  SELECT VENDOR_fullname as name, SUM(POTENTIAL_RECOVERY) as recovery
  FROM COMPLETE
  GROUP BY VENDOR_fullname
  ORDER BY SUM(POTENTIAL_RECOVERY) DESC
  LIMIT 10
)
UNION ALL
(
  SELECT 'All others' as name, SUM(subtotal) as recovery
  FROM 
  (
    SELECT SUM(POTENTIAL_RECOVERY)
    FROM COMPLETE
    GROUP BY VENDOR_fullname
    ORDER BY SUM(POTENTIAL_RECOVERY) DESC
    LIMIT 10, 18446744073709551615
  ) as subtotal;
)

这篇关于MYSql 前 10 名及其他合计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！