sql-Mysql：在多行上左联接并检索1行

MovieID -- Name
1          -- Movie1
2          -- Movie2

MovieID -- Type
1          -- DVD
1          -- Bluray
1          -- VCD
2          -- DVD

SELECT Movies.Name,
IF(TYPE = \'DVD\', 1, 0 ) AS DVD,
IF(TYPE = \'Bluray\', 1, 0 ) AS Bluray,
IF(TYPE = \'VCD\', 1, 0 ) AS VCD
FROM Movies LEFT JOIN Types ON Movies.MovieID = Types.MovieID

Movies.Name -- DVD -- Bluray -- VCD
Movie1          -- 1     -- 0        -- 0
Movie1          -- 0     -- 1        -- 0
Movie1          -- 0     -- 0        -- 1
Movie2          -- 1     -- 0        -- 0

Movie1          -- 1     -- 1        -- 1
Movie2          -- 1     -- 0        -- 0

SELECT Movies.Name,
    group_concat(type separator \' - \') as type
FROM Movies 
    LEFT JOIN Types ON Movies.MovieID = Types.MovieID
group by Movies.Name

SELECT m.Name, 
       SUM(CASE WHEN t.Type = \'DVD\' THEN 1 ELSE 0 END) AS DVD,
       SUM(CASE WHEN t.Type = \'Bluray\' THEN 1 ELSE 0 END) AS Bluray,
       SUM(CASE WHEN t.Type = \'VCD\' THEN 1 ELSE 0 END) AS VCD
    FROM Movies m
        LEFT JOIN Types t 
            ON m.MovieID = t.MovieID
    GROUP BY m.Name