mysql - 根据上一列添加另一个不同的列

Question

假设我在数据库中有一个这样的表

Name  Day  VALUE1  VALUE2
========================
A     mon      1       2
A     fri      2       2

我想要它喜欢

Name  VALUE1(mon)  VALUE2(mon)  VALUE1(fri)  VALUE2(fri)
=====================================================
A        1             2            2            2

我可以做这样的东西吗？如果可以的话，请告诉我如何用查询来做这个

标签： mysqlsqlpostgresql

Answer 1

您可以使用数据透视查询：

SELECT
    Name,
    MAX(CASE WHEN DAY = 'mon' THEN VALUE1 END) AS "Value1(mon)",
    MAX(CASE WHEN DAY = 'mon' THEN VALUE2 END) AS "Value2(mon)",
    MAX(CASE WHEN DAY = 'fri' THEN VALUE1 END) AS "Value1(fri)",
    MAX(CASE WHEN DAY = 'fri' THEN VALUE2 END) AS "Value2(fri)"
FROM yourTable
GROUP BY Name;