mysql - 修复 MySQL 查询

Question

我的查询有问题，请检查:http://sqlfiddle.com/#!9/8102d/1

我的预期结果是这样的:

PartID           |IdMesin|MR|MOSGN|NOP|MT|MS|MOSTRO|NF |MORREST|MOR |NJ|TRY |
AWDX-FG001-HC-KGX|BH 260K|0 |   0 | 0 |0 |0 |0     |600|0      |1200|0 |9000|
AWDX-FG001-HC-KGX|BH 260Q|0 |   0 | 0 |0 |0 |0     |0  |0      |0   |0 |3600|
AWDX-FG002-HN-KGX|BH 260K|3600|0  | 0 |0 |0 |0     |0  |0      |0   |0 |0   |
BLAP-FG100-GS-KGX|BS 80F |0 | 0  |3600| 0|0 |0     |0  |0      |0   | 0|0   |
EIXX-FG000-MX-KGX|BS 80F |0 | 0  |  0 |0 |0 |0  |1800  |0      |0   |0 |0   | 
HMXX-FG330-XX-KGX|BS 320E|0 |0    |0  |0 |0 |3600  |0  |0      |0   |0 |0   |
MCMX-FG638-PX-KGX|SM 120F|0 |3600 |0  |0 |0 |0     |0  |0      |0   |0 |0   |

希望您能帮我解决这个问题，我们将不胜感激。

Answer 1

您需要条件聚合，SO 和其他地方有很多示例，这是另一个 -

SELECT
    PartID,
    IdMesin,
    SUM(CASE WHEN IdDowntime = 'MR001' THEN TIME_TO_SEC(maketime(durasi + 0,substring_index(durasi, 'Jam ', - 1) + 0,   0)) ELSE 0 END) AS MR,
    SUM(CASE WHEN IdDowntime = 'MOSGN01' THEN TIME_TO_SEC(maketime(durasi + 0,substring_index(durasi, 'Jam ', - 1) + 0, 0)) ELSE 0 END) AS MOSGN,
    SUM(CASE WHEN IdDowntime = 'NO001' THEN TIME_TO_SEC(maketime(durasi + 0,substring_index(durasi, 'Jam ', - 1) + 0,   0)) ELSE 0 END) AS NOP,
    SUM(CASE WHEN IdDowntime = 'MT001' THEN TIME_TO_SEC(maketime(durasi + 0,substring_index(durasi, 'Jam ', - 1) + 0,   0)) ELSE 0 END) AS MT,
    SUM(CASE WHEN IdDowntime = 'MOS01' THEN TIME_TO_SEC(maketime(durasi + 0,substring_index(durasi, 'Jam ', - 1) + 0,   0)) ELSE 0 END) AS MS,
    SUM(CASE WHEN IdDowntime = 'MOSTR01' THEN TIME_TO_SEC(maketime(durasi + 0,substring_index(durasi, 'Jam ', - 1) + 0, 0)) ELSE 0 END) AS MOSTRO,
    SUM(CASE WHEN IdDowntime = 'NF001' THEN TIME_TO_SEC(maketime(durasi + 0,substring_index(durasi, 'Jam ', - 1) + 0,   0)) ELSE 0 END) AS NF,
    SUM(CASE WHEN IdDowntime = 'MOSR01' THEN TIME_TO_SEC(maketime(durasi + 0,substring_index(durasi, 'Jam ', - 1) + 0,  0)) ELSE 0 END) AS MORREST,
    SUM(CASE WHEN IdDowntime = 'MOR01' THEN TIME_TO_SEC(maketime(durasi + 0,substring_index(durasi, 'Jam ', - 1) + 0,   0)) ELSE 0 END) AS MOR,
    SUM(CASE WHEN IdDowntime = 'NJ001' THEN TIME_TO_SEC(maketime(durasi + 0,substring_index(durasi, 'Jam ', - 1) + 0,   0)) ELSE 0 END) AS NJ,
    SUM(CASE WHEN IdDowntime = 'TRY01' THEN TIME_TO_SEC(maketime(durasi + 0,substring_index(durasi, 'Jam ', - 1) + 0,   0)) ELSE 0 END) AS TRY
FROM    trans_lhpdtdw
WHERE DivisiId = 'IJ001' AND Tanggal = '2017-10-02'
GROUP BY    PartID, IdMesin
ORDER BY PartID