将键值列分组为一行

Question

我正在尝试从SQLite表中提取数据，该表将键值对存储在双列中。例如，用钥匙foo，bar，man，和row，该表将如下所示：

| _id | external_id | 关键 值|
| ----- | ------------- || ---- | ------- |
| 1 | 12345 | foo | 牛|
| 2 | 12345 | 酒吧 哞|
| 3 | 12345 | 男人 孔
| 4 | 12345 | 排| 船|
| 5 | 67980 | foo | abc |
| 6 | 67890 | 酒吧 def |
| 7 | 67890 | 男人 ghi |
| 8 | 67890 | 排| jkl |

我想执行一个查询，external_id以行作为键，值作为行，每行都给我。像这样：

| external_id | foo | 酒吧 男人 排|
| ------------- | ----- | -------- || ------ | ------ |
| 12345 | 牛| | 孔 船|
| 67890 | abc | def | ghi | jkl |

我唯一能想到的解决方案是每个键的联接：

SELECT a.external_id, b.foo, c.bar, d.main, e.row 
FROM myTable AS a
LEFT JOIN 
  (SELECT external_id, key AS foo
   FROM myTable
   WHERE key="foo") AS b
   ON a.external_id = b.external_id
...
LEFT JOIN 
  (SELECT external_id, key AS row
   FROM myTable
   WHERE key="row") AS e
   ON a.external_id = e.external_id
GROUP BY a.external_id

有一个更好的方法吗？

Answer 1

另一个可用的选项是使用 条件聚合 ：

SELECT external_id,
       MAX(CASE WHEN key = 'foo' THEN value END) AS foo,
       MAX(CASE WHEN key = 'bar' THEN value END) AS bar,
       MAX(CASE WHEN key = 'man' THEN value END) AS man,
       ... etc
FROM mytable 
GROUP BY external_id