最近我遇到了一个具有以下结构的项目
http://sqlfiddle.com/#!2/3a4cf/1
一切正常,我想我可以在PHP端对事物进行排序,但是我的问题是,使用单个查询是否可能
number, name, age, email, gender
--------------------------------------------
123456, Test1, 24, [email protected], m
123457, Test2, 23, [email protected], f
123458, Test3, 22, [email protected], m
123459, Test4, 21, [email protected], f
123460, Test5, 25, [email protected], m
将列名称视为实际字段。
我尝试了多个左/右联接(与数量一样多的字段),但是可以上升到无限大声笑...即具有100个字段(将是200个联接)以在单行中获得结果(仍然没有列)名称为field_value)。
我研究了以下内容(可能重复):
但是并没有对我说太多(不是在mysql :()中经历过的。
如果可能的话,如果有人可以指导我如何实现这一目标,我将不胜感激。
提前致谢。
我想你要这个查询
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
动态的
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;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句