mysql - 获取一些行作为列

我有2张桌子：

table1:
         |id|create_date|title  |type  |
         -------------------------------
         | 1|2015-07-20 |bla-bla|TICKET|

table2_meta:
         |id|table1_id|meta_name|meta_value|
         -----------------------------------
         | 1| 1       | status  | new      |
         | 2| 1       | priority| low      |
         | 3| 1       | owner   | alex     |

Now, i wish to select it like this:

            |id|create_date|title  |status|priority|owner|
            |1 |2015-07-20 |bla-bla|new   |low     |alex |

My solution is:

SELECT
      t1.id,
      t1.event_date,
      case when m.meta_name="status" then m.meta_value end as status,
      case when m.meta_name="priority" then m.meta_value end as priority,
      case when m.meta_name="owner" then m.meta_value end as owner
FROM table1 as t1
LEFT JOIN table2_meta as m
      on m.table1_id=t1.id
WHERE t1.type='TICKET'
GROUP BY t1.id

因此，它有效。但是似乎有点难看。
我的问题是：
此选择还有其他解决方案吗？如何使其更具生产力？

最佳答案

嘿，您可以尝试以这种方式旋转表SQLFIDDLE

set @sql = null;
select
  group_concat(distinct
    concat(
      'max(case when meta_name = ''',
      meta_name,
      ''' then value end) AS ',
      concat(meta_name)
    )
  ) into @sql
FROM table1 as t1
LEFT JOIN table2 as m
      on m.table1_id=t1.id
WHERE t1.type='TICKET';

set @sql = concat('select t1.id,', @sql, ' FROM table1 as t1
LEFT JOIN table2 as m
      on m.table1_id=t1.id
WHERE t1.type=''TICKET''
GROUP BY t1.id
');

prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

关于mysql - 获取一些行作为列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/31425630/