MySQL动态行值作为列名 [英] Mysql dynamic row values as column names

问题描述

最近我遇到了一个具有以下结构的项目

Recently i ran into a project with following structure

http://sqlfiddle.com/#!2/3a4cf/1

一切正常，我想我可以在PHP端对事物进行排序，但是我的问题是，使用单个查询是否可能

all works ok, i think i can sort the things on PHP end, but my question is, is it possible with a single query

number, name, age, email, gender
--------------------------------------------
123456, Test1, 24, [e-mail], m
123457, Test2, 23, [e-mail], f
123458, Test3, 22, [e-mail], m
123459, Test4, 21, [e-mail], f
123460, Test5, 25, [e-mail], m

将列名视为实际field.field_name

considering column names to be the actual field.field_name

我尝试了多个左/右联接(与字段的数目一样多)，但是最多可以达到无限大声笑...即具有100个字段(将是200个联接)以在单行中获得结果(仍然没有列名作为field_value).

I tried with multiple left/right joins (as many fields as the number has) but that can go up to infinity lol ... i.e. having 100 fields that will be 200 joins to get the result in a single row (still with no column names as the field_value).

我查看了以下内容(可能重复):

I took a look into following (possible duplicate):

mysql选择动态行值作为列名，另一列作为值

但是对我讲的并不多(不是mysql :()中的经验.

but doesnt talk much to me (not that experienced in mysql :( ).

如果可以的话，如果有人可以指导我实现目标，我将不胜感激.

If possible i'd be grateful if anyone can guide me a bit on how to achieve it.

谢谢.

推荐答案

我认为您想要此查询

SELECT SQL_NO_CACHE
    n.number, n.name, 
MAX(CASE WHEN f1.field_name='age' THEN nf1.value END) as Age,
MAX(CASE WHEN f1.field_name='email' THEN nf1.value END) as Email,
MAX(CASE WHEN f1.field_name='gender' THEN nf1.value END) as Gender
FROM
    number AS n
        LEFT JOIN
    number_field AS nf1 ON n.id = nf1.number_id
        RIGHT JOIN
    field AS f1 ON f1.id = nf1.field_id
WHERE
    1 = 1
GROUP BY n.number,n.name
ORDER BY number

FIDDLE

动态

SET SESSION group_concat_max_len = 1000000;

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN f1.field_name= ''',
      f1.field_name,
      ''' THEN nf1.`value` END) AS ',
      f1.field_name
    )
   )INTO @sql
FROM  
    number_field AS nf1 
        RIGHT JOIN
    field AS f1 ON f1.id = nf1.field_id
ORDER BY f1.field_name;


SET @sql=CONCAT('SELECT n.number, n.name, ',@sql,' FROM
  number AS n
        LEFT JOIN
    number_field AS nf1 ON n.id = nf1.number_id
        RIGHT JOIN
    field AS f1 ON f1.id = nf1.field_id
GROUP BY n.number,n.name
ORDER BY number');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

FIDDLE

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