MYSql前10名及其他合计

Question

我有这个查询，可以很好地工作，但我只需要前10个供应商。

然后，我需要所有剩余的总数在“所有其他”行。

如果不使用LIMIT 10, 18446744073709551615进行单独的查询，我该如何执行此操作

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

Answer 1

郑重声明，我同意@DamienBlack的评论，然而，如果必须在一个查询中完成，UNION ALL就可以做到：

(
  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;
)

Answer 2

您可以使用union：

SELECT VENDOR_fullname as anme,SUM(POTENTIAL_RECOVERY)
FROM COMPLETE
GROUP BY VENDOR_fullname
ORDER BY SUM(POTENTIAL_RECOVERY) DESC
LIMIT 10

UNION ALL

SELECT 'All Others' as name,SUM(POTENTIAL_RECOVERY)
FROM COMPLETE
ORDER BY SUM(POTENTIAL_RECOVERY) DESC
LIMIT 10, 18446744073709551615