MySQL计数全部超出限制

Question

我有一个MySQL查询，它计算我们从前五个客户那里工作的小时数。这可以很好地工作，并且我正在将数据绘制在图形上。我试图实现的是查询中的计算，它返回第六个结果集，该结果集是不在前五个小时中的所有小时的总和。即“其他”结果，因此所有6个结果的总和将等于projects.hours的总和。

我的问题是：

SELECT
  SUM(projects.hours) AS total_hours
FROM projects
GROUP BY projects.companyID
ORDER BY total_hours DESC
LIMIT 5

Answer 1

SELECT
SUM(projects.hours) AS total_hours,
(SELECT SUM(projects.hours) FROM projects) AS all_hours
FROM 
projects
GROUP BY 
projects.companyID
ORDER BY total_hours DESC
LIMIT 5

在应用程序中从all_hours中减去total_hours。

Answer 2

SELECT
  1 no,
  projects.company,
  SUM(projects.hours) AS total_hours
FROM 
  projects
GROUP BY 
  projects.companyID
ORDER BY
  total_hours DESC
LIMIT 5

UNION ALL

SELECT
  2 no,
  'Others',
  SUM(projects.hours) AS total_hours
FROM 
  projects
WHERE projects.companyID NOT IN 
( SELECT companyID 
  FROM 
  (  SELECT
      projects.companyID
    FROM 
      projects
    GROUP BY 
      projects.companyID
    ORDER BY
      SUM(total_hours) DESC
    LIMIT 5
  ) AS tmp
)

Answer 3

将这两种方法结合起来如何?通过添加汇总，我们可以消除对IN的需求

(我不确定您是否可以使用limit进行rollup，但我这里没有MYSQL，所以无法测试，抱歉)

注意:我希望这会返回一个空的公司id，它包含其他行的总数，

SELECT companyID, 
CASE WHEN companyID IS NULL THEN 
    sum(Total_hours)
ELSE
    sum(Total_hours) * -1
END

FROM 
(           
    SELECT companyID,SUM(projects.hours) AS total_hours,  
    FROM  projects 
    GROUP BY  projects.companyID 
    ORDER BY total_hours DESC WITH ROLLUP LIMIT 5 
    UNION ALL
    SELECT null,SUM(projects.hours)*-1 total_hours FROM projects  
)
GROUP BY companyID