MySQL选择列名称和值作为字段 [英] MySQL select column name and value as a field

问题描述

我有一个看起来像这样的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

这篇关于MySQL选择列名称和值作为字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！