键值对，每个键的列，键作为columnheader

I have a table field_list like this:

我有一个这样的表field_list:

field_id|obj_id|field_key|field_content
----------------------------------------    
     1  |   1  |   123   |   Title_A
     2  |   1  |   234   |   IP_A
     3  |   1  |   567   |   DNS_A
     4  |   2  |   123   |   Title_B
     5  |   2  |   456   |   IP_B
     6  |   2  |   789   |   DNS_B
     7  |   3  |   123   |   Title_C
     8  |   3  |   456   |   IP_C
     9  |   3  |   789   |   DNS_C

What I need is this:

我需要的是:

obj_id  |123    |456  |789
----------------------------------------    
    1   |Title_A|IP_A |DNS_A
    2   |Title_B|IP_B |DNS_B
    3   |Title_C|IP_C |DNS_C

I had several ideas, how to achieve this (create new table or view, use scirpts, etc.). Unfortunately my SQL-Skills are not very professional and I never know if I am working in the right direction.

我有几个想法，如何实现它(创建新的表或视图、使用scirpts等等)。不幸的是，我的sql技能不是很专业，我也不知道我的工作方向是否正确。

Background: The mySql database is used for storing information about webpages. There is a table 'obj', and a table 'field_list'. 'obj_id' is foreign-key from obj table. 'field_id' is primary-key from field_list.

背景:mySql数据库用于存储关于网页的信息。有一个表'obj'和一个表'field_list'。“obj_id”是obj表中的外键。“field_id”是field_list的主键。

I managed to get something that looks what I want with concatenate and seperators, but not as result with many columns.

我设法得到了一些看起来像我想要的连接和分隔符的东西，但结果并不是很多列。

If there is no easy answer, or to many - perhaps someone can lead me into the right direction.

如果没有简单的答案，或者对许多人来说——也许有人能引导我找到正确的方向。

Please excuse my bad english and grammar. This is my first * question - I hope you have patience with me. I will modify my question tomorrow if something is not clear.

请原谅我的英语和语法差。这是我第一个*的问题，希望你对我有耐心。如果有什么不清楚的地方，我明天会修改我的问题。

THX!

谢谢!

1 个解决方案

#1

You could try something like this, but performance might not be perfect...

你可以尝试这样的东西，但是性能可能并不完美……

This table model excells in searching, not on it's table column representation:

这个表格模型在搜索时表现出色，而不是在它的表格列表示法上:

SELECT object_id,
MAX( IF( field_key = 123, field_content, NULL) ) AS col_123,
MAX( IF( field_key = 234, field_content, NULL) ) AS col_234,
MAX( IF( field_key = 345, field_content, NULL) ) AS col_345,
MAX( IF( field_key = 456, field_content, NULL) ) AS col_456,
MAX( IF( field_key = 567, field_content, NULL) ) AS col_567
FROM field_list
GROUP BY object_id

#1