mysql - 内连接消除重复

Question

架构:

查询:

 SELECT avto.Marka,SUM(OtrabotanoMachine_hours) as 'work hours',SUM(sec.Hours) as Downtime,CONVERT(group_concat(concat(prostoy.Prichina,'=',sec.Hours) separator ';') USING 'utf8') as 'downtime reasons',CONVERT(group_concat(concat(VipolnennayaRabota,'=',V_raboty) separator ';') USING 'utf8') as 'performed works'
FROM
jos_addRabotyAndProstoyMain main 
INNER JOIN avto ON main.Marka=avto.ID_Avto
INNER JOIN (
   SELECT id_fk, SUM(Hours) AS Hours, Prostoy
   FROM jos_addRabotyAndProstoySecond 
   GROUP BY id_fk,Prostoy) sec ON sec.id_fk = main.id 
INNER JOIN prostoy ON sec.Prostoy = prostoy.ID_Prosyoy
GROUP BY avto.Marka

错误结果:

 | Marka   | work hours  |downtime| downtime reasons      | performed works|
 |JCB-90QX |      44     |    7   |lack of materials=2;   | work=51;       |
 |         |             |        |machine malfunction=1; | work=51;       |               
 |         |             |        |Others=3;              | work=51;       |
 |         |             |        |unscheduled repairs=1  | work=51;       |

“工作时间”和“执行的作品”列包含重复项。如何去重？

我需要这样的结果:

| Marka   | work hours  |downtime| downtime reasons      | performed works|
|JCB-90QX |      11     |    7   |lack of materials=2;   | work=51;       |
|         |             |        |machine malfunction=1; |                |               
|         |             |        |Others=3;              |                |
|         |             |        |unscheduled repairs=1  |                |

更新SQL Fiddle

Answer 1

我认为解决方案是向要使用唯一数据的列添加 DISTINCT 子句。

试试这个(取自您的 SQL Fiddle):

 SELECT avto.Marka,SUM(distinct OtrabotanoMachine_hours) as 'work hours',SUM(sec.Hours) as Downtime,CONVERT(group_concat(concat(prostoy.Prichina,'=',sec.Hours) separator ';') USING 'utf8') as 'downtime reasons',CONVERT(group_concat(distinct concat(VipolnennayaRabota,'=',V_raboty) separator ';') USING 'utf8') as 'performed works'
FROM
jos_addRabotyAndProstoyMain main 
INNER JOIN avto ON main.Marka=avto.ID_Avto
INNER JOIN (
   SELECT id_fk, SUM(Hours) AS Hours, Prostoy
   FROM jos_addRabotyAndProstoySecond 
   GROUP BY id_fk,Prostoy) sec ON sec.id_fk = main.id 
INNER JOIN prostoy ON sec.Prostoy = prostoy.ID_Prosyoy
GROUP BY avto.Marka

但是，重要的是要注意，我不是 100% 确定 OtrabotanoMachine_hours 是如何设置的。如果有可能存在两个具有相同值的合法独立条目，它将对它们进行重复数据删除。