MySQL选择列名称和值作为字段
问题描述：
我有一个看起来像这样的mysql表:
I have a mysql table that looks something like this:
id | PO | DAP | MEDIA
---|----|-------|------
1 | 2 | 34 | 64
2 | 6 | 53 | 23
我希望能够查询到多行，每列一行.例如:
I would like to be able to query get multiple rows, one for each column. E.g:
SELECT column_name as column, column_value as value FROM my_table;
哪个会给我的?
PO=2,DAP=34,MEDIA=54,PO=6,DAP=53,MEDIA=23
我需要使用什么来制定这样的查询?
What would I need to use to formulate a query like this?
答
必须首先CONCAT每个指定字段的数据，并对结果应用GROUP_CONCAT.
You have to first CONCAT the data of each specified field and apply GROUP_CONCAT ON the result.
查询
SELECT GROUP_CONCAT(temp_col) FROM
(
SELECT 1 as 'temp_id',
CONCAT(
CONCAT('PO=', PO),
',',
CONCAT('DAP=', DAP),
',',
CONCAT('MEDIA=', MEDIA)
) AS 'temp_col'
FROM test
) temp
GROUP BY temp_id
签出 SQLFIDDLE
Check Out SQLFIDDLE