mysql求和并显示所有注册表

Question

im试图求和并显示mysql中的所有注册表。我有这个查询：

select `deliverables`.*, 
       `users`.`first_name`, 
       `users`.`last_name` 
from `deliverables` 
inner join `users` on `users`.`id` = `deliverables`.`user_id` 
where `deliverables`.`specialty_id` = '11' and 
      `deliverables`.`role_id` <> '1' 

输出为：

file_code  file_name       dedicated_hours
0001asder  test-file.docx  4
0001as234w asdf.doc        2
jgfjh2546  test.docx       4
0001asder  test-file.docx  1
0001asder  test-file.docx  0
0001asder  test-file.docx  0

我需要对file_code相等的情况下的dedicated_hours求和，这样它看起来应该是这样的：

file_code  file_name       dedicated_hours  sum
0001asder  test-file.docx  4                5
0001as234w asdf.doc        2                2
jgfjh2546  test.docx       4                4
0001asder  test-file.docx  1                5
0001asder  test-file.docx  0                5
0001asder  test-file.docx  0                5

im使用sum(dedicated_hours(并按file_code分组，但它只显示：

file_code  file_name       dedicated_hours  sum
0001asder  test-file.docx  4                5
0001as234w asdf.doc        2                2
jgfjh2546  test.docx       4                4

如何同时汇总和显示所有注册表？

Answer 1

您的MySQL版本(5.0.12(不支持Window函数(升级以获得最新的好东西应该是一个不错的激励：(

然而，我们可以使用派生表来单独确定file_code的聚合SUM()。然后，我们可以简单地连接回主表，以显示sum列。

SELECT 
  d.file_code,
  d.file_name, 
  d.dedicated_hours,  
  dt.sum, 
  u.first_name, 
  u.last_name 
FROM deliverables AS d 
JOIN users AS u 
  ON u.id = d.user_id 
JOIN
(
  SELECT file_code, 
         SUM(dedicated_hours) AS sum 
   WHERE speciality_id = '11' AND 
         role_id <> '1'
   GROUP BY file_code
) AS dt 
  ON dt.file_code = d.file_code
WHERE d.speciality_id = '11' AND 
      d.role_id <> '1'

---

MySQL 8.0.2及以上版本的解决方案只需使用SUM(..) OVER (..)

SELECT 
  d.file_code,
  d.file_name, 
  d.dedicated_hours,  
  SUM(d.dedicated_hours) OVER (PARTITION BY d.file_code) AS sum, 
  u.first_name, 
  u.last_name 
FROM deliverables AS d 
JOIN users AS u 
  ON u.id = d.user_id 
WHERE d.speciality_id = '11' AND 
      d.role_id <> '1'