如何将Oracle pivot函数查询转换为MySQL查询

Question

请查找以下查询，因为MySQL中没有要使用的透视函数。请提供相同的MySQL查询。请在下面的表格图片和预期数据查询后找到链接。

SELECT
   *
FROM
   (
       SELECT
           *
       FROM
           (
               SELECT
                   X.*,
                   Y.status,
                   Y.create_date,
                   Z.x_some_id
               FROM
                   X csv
                   LEFT JOIN Y lsh ON X.id = Y.x_id
                                                          AND Y.type = 'STATUS'
                   LEFT JOIN Z vi ON X.some_id = Z.x_some_id
               WHERE
                   X.some_col = 'abc'
           ) PIVOT (
               MAX ( create_date )
               FOR status
               IN ( 'CONFIRMED',
               'UNCONFIRMED' )
           )
   )
ORDER BY
   date ASC```

 [1]: https://i.stack.imgur.com/LmNJt.png

Answer 1

只需使用条件聚合：

SELECT X.ID,  -- add the extra columns you want here
       MAX(CASE WHEN Y.status = 'CONFIRMED' THEN Y.create_date END) as confirmed,
       MAX(CASE WHEN Y.status = 'UNCONFIRMED' THEN Y.create_date END) as unconfirmed
FROM X LEFT JOIN
     Y
     ON X.id = Y.x_id AND 
        Y.type = 'STATUS' LEFT JOIN
     Z
     ON X.some_id = Z.x_some_id
WHERE X.some_col = 'abc'
GROUP BY X.id;  -- whatever columns you want to lead the