mysql - 同一表中的 SQL 嵌套组

Question

我的数据看起来像这样:

id      key         value
--------------------------------
1       country     us
1       state       nj
1       city        trenton
2       country     us
2       state       nj
2       city        springfield
3       country     us
3       state       nj
3       city        belmar
4       country     us
4       state       ny
4       city        new york
4       country     us
4       state       ny
4       city        white plains
4       country     canada
4       state       ontario
4       city        toronto
5       country     us
5       state       nj
5       city        trenton

我正在尝试编写一个 SQL 查询以按独特的国家、州、城市进行分组

country     state       city
--------------------------------------
us          nj          trenton
us          nj          springfield
us          nj          belmar
us          ny          new york
us          ny          white plains
canada      ontario     toronto

我能够获得不同的国家、州和城市，但我不确定如何将它们与 ID 列连接在一起。

Answer 1

值得郑重声明的是，这是一个数据困惑，需要“规范化”。话虽如此，您可以尝试:

    SELECT cn.value as country, st.value as state, ct.value as city
    FROM table cn
    LEFT JOIN table st ON cn.id = st.id
    LEFT JOIN table st ON cn.id = ct.id
    WHERE cn.key = 'country'
    AND st.key = 'state'
    AND ct.key = 'city'
    ORDER BY cn.value , st.value , ct.value