mysql - Sql 结果后将两列相同的 id 相加

Question

我的结果是这样的:

+-------+--------+--------+-----+--------------+
| Count | Equipe | IdTeam | Id  |     Name     |
+-------+--------+--------+-----+--------------+
|    21 | A      |      1 | 358 | closs on D   |
|   107 | B      |      2 | 358 | closs D      |
|    15 | A      |      1 | 357 | Asos closs D |
|     9 | B      |      2 | 357 | Asos closs D |
+-------+--------+--------+-----+--------------+

如果 ID 相同，我需要此total = count + count

+-------+--------+---------+---------+-----+---------------+
| total |  Count |  Equipe |  IdTeam |  Id |      Name     |
+-------+--------+---------+---------+-----+---------------+
|   128 |     21 |  A      |       1 | 358 |  closs D      |
|   128 |    107 |  B      |       2 | 358 |  closs D      |
|    24 |     15 |  A      |       1 | 357 |  Asos closs D |
|    24 |      9 |  B      |       2 | 357 |  Asos closs D |
+-------+--------+---------+---------+-----+---------------+

所以我添加这个:

sum(CASE WHEN t2_aop_appel.t2_aop_Zone_Id = t2_aop_appel.t2_aop_Zone_Id 
         THEN 1 
         ELSE 0 
    end) as total

所以我尝试这个:

    SELECT sum(CASE WHEN t2_aop_appel.t2_aop_Zone_Id=t2_aop_appel.t2_aop_Zone_Id THEN 1 ELSE 0 end) as total,count(*) as Count,t2_aop_equipe.Name as Equipe,t2_aop_equipe.Id as IdTeam,t2_aop_appel.t2_aop_Zone_Id as Id,t2_aop_zone.Name  
FROM t2_aop_appel   
join t2_aop_equipe on t2_aop_appel.t2_aop_Equipe_Id = t2_aop_equipe.Id
  join t2_aop_zone on t2_aop_appel.t2_aop_Zone_Id = t2_aop_zone.Id  
  WHERE  t2_aop_appel.t2_aop_Usine_Id = 1 AND t2_aop_appel.t2_aop_Departement_Id = 1 AND t2_aop_appel.t2_aop_Atelier_Id = 3 AND
  t2_aop_appel.t2_aop_Ligne_Id = 13 AND t2_aop_appel.t2_aop_Appel_Type_Id  in(1) AND t2_aop_appel.t2_aop_Equipe_Id  in(1,2) AND
  t2_aop_appel.t2_aop_Category_Id  in(1,2,3,4,5,6) AND t2_aop_appel.Appel >= '2017-11-20' AND t2_aop_appel.Appel <= '2017-11-26'  
 Group By  t2_aop_equipe.Name,t2_aop_zone.Id 

结果:

我在“计数”列中得到相同的结果

有人可以帮助我，谢谢你

Answer 1

您需要一个子查询

SELECT *, (SELECT SUM(Count) 
           FROM YourTable t2
           WHERE t2.Id  = t1.Id ) as Total
FROM YourTable t1

注意:在这种情况下，YourTable 是您的查询生成的第一个结果。因此，您要么重复查询，要么将其保存为临时表。