带有键/值对的 MySQL 表，获取键作为列名 [英] MySQL table with key/value pairs, get keys as column names

问题描述

我有一个 MySQL 数据库，可以在其中执行以下查询:

I have a MySQL database where I can do the following query:

mysql> SELECT Name, Value from info WHERE ID = 110506;

+-------+-----------+
| Name  | Value     |
+-------+-----------+
| start | 228196    |
| stop  | 228318    |
| fwd   | 0         | 
+-------+-----------+
3 rows in set (0.00 sec)

我正在尝试构建一个查询，结果将是

I am trying to construct a query where the result would be

+--------+--------+-----+
| start  | stop   | fwd |
+--------+--------+-----+
| 228196 | 228318 | 0   |
+------- +--------+-----+
1 row in set (0.00 sec)

我事先不知道我的名称列中的名称是什么，所以我需要以某种方式从我的 SELECT 查询的结果中动态设置它们.我该怎么做?

I do not know in advance what the names in my name column will be, so I need to somehow dynamically set them from the result of my SELECTquery. How do I do that?

推荐答案

试试这个

SELECT
    MAX(CASE WHEN name = 'start' THEN value END) AS `start`,
    MAX(CASE WHEN name = 'stop' THEN value END) AS `stop`,
    MAX(CASE WHEN name = 'fwd' THEN value END) AS `fwd`
FROM info
WHERE id = 110506;

这篇关于带有键/值对的 MySQL 表，获取键作为列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！