将表行合并为具有原始行的列标题的列

本文介绍了将表行合并为具有原始行的列标题的列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有三个经过简化的MYSQL表，其以下各列:

I have three MYSQL tables, simplified, with following columns:

ModuleEntries (MDE_ID) ModuleFields (MDF_ID, MDF_Label) ModuleEntryFieldValues (MFV_ID, MFV_MDE_ID, MFV_MDF_ID, MFV_Value)

从简化的列列表中可以看到，ModuleEntryFieldValues是说明对于条目，此字段具有此值"的表. 现在，我想在一个固定"记录集中检索此信息.我设法使事情正常进行，但并不能完全满足我的要求. 在另一篇SO文章的帮助下，我设法通过使用游标使可变数量的动态列开始工作.

As you can see from the simplified column list, ModuleEntryFieldValues is the table which states that "for an entry, this field has this value". Now, I would like to retrieve this information in one "flat" recordset. I have managed to get things working, but not entirely to what i want. With the help of a different SO article, I managed to get the dynamic, variable amount of columns to work, by using a cursor.

declare finish int default 0; declare fldid int; declare str varchar(10000) default 'SELECT *,'; declare curs cursor for select MDF_ID from ModuleFields group by MDF_ID; declare continue handler for not found set finish = 1; open curs; my_loop:loop fetch curs into fldid; if finish = 1 then leave my_loop; end if; set str = concat(str, 'max(case when MFV_MDF_ID = ',fldid,' then MFV_Value else null end) as field_',fldid,','); end loop; close curs; set str = substr(str,1,char_length(str)-1); set @str = concat(str, ' from ModuleEntries LEFT join ModuleEntryFieldValues ON MDE_ID = MDF_MDE_ID GROUP BY MDE_ID'); prepare stmt from @str; execute stmt; deallocate prepare stmt;

这段代码没有要做的是，允许我将MDF_Label的列值作为行的实际列标题.现在，上面的代码为我提供了"field_1，field_2，..." 是否可以联接这三个表，并以MDF_Label作为现在已联接表中的列的行的列标题? 我想要这个...

What this code doesn't do, is allow me to put the column values of MDF_Label as the actual column headers of the rows. Now, the above code gives me "field_1, field_2, ..." Is it possible to join these three tables, and have the MDF_Label as column header for the rows that are now columns in the joined table ? I want this...

ModuleEntries | ModuleFields | ModuleEntryFieldValues ------------- | ------------------ | ----------------------------------- MDE_ID | MDF_ID - MDF_Label | MFV_MDE_ID - MFV_MDF_ID - MFV_Value 1 | 1 - Height | 1 - 1 - 120cms | 2 - Width | 1 - 2 - 30cms | 3 - Weight |

进入此

Recordset --------- MDE_ID - Height - Width - Weight 1 - 120cms - 30cms - null

我希望我的问题足够清楚.如果没有，请发表评论，如果可以的话，我会在需要的地方提供更多信息.

I hope my question was clear enough. If not please comment and I will give more information where needed, if I can.

推荐答案

我只使用 GROUP_CONCAT() 而不是游标以生成准备好的语句:

I'd just use GROUP_CONCAT() instead of cursors to generate the prepared statement:

SELECT CONCAT( ' SELECT MDE_ID,' , GROUP_CONCAT( 't', MDF_ID, '.MFV_Value AS `', REPLACE(MDF_Label, '`', '``'), '`' ) ,' FROM ModuleEntries ' , GROUP_CONCAT( 'LEFT JOIN ModuleEntryFieldValues AS t', MDF_ID, ' ON t', MDF_ID, '.MFV_MDE_ID = ModuleEntries.MDE_ID AND t', MDF_ID, '.MFV_MDF_ID = ', MDF_ID SEPARATOR ' ') ) INTO @qry FROM ModuleFields;

保存以进行空白编辑以使其更具可读性，并且示例数据@qry将包含:

Save for whitespace editing to make it more readable, with your sample data @qry would then contain:

SELECT MDE_ID, t1.MFV_Value AS `Height`, t2.MFV_Value AS `Width`, t3.MFV_Value AS `Weight` FROM ModuleEntries LEFT JOIN ModuleEntryFieldValues AS t1 ON t1.MFV_MDE_ID = ModuleEntries.MDE_ID AND t1.MFV_MDF_ID = 1 LEFT JOIN ModuleEntryFieldValues AS t2 ON t2.MFV_MDE_ID = ModuleEntries.MDE_ID AND t2.MFV_MDF_ID = 2 LEFT JOIN ModuleEntryFieldValues AS t3 ON t3.MFV_MDE_ID = ModuleEntries.MDE_ID AND t3.MFV_MDF_ID = 3

然后可以准备并执行该语句:

One can then prepare and execute that statement:

PREPARE stmt FROM @qry; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @qry = NULL;

将产生以下结果:

MDE_ID Height Width Weight 1 120cms 30cms (null)

在 sqlfiddle 上查看.