mysql - 获取一些行作为列

Question

我有 2 张 table :

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 

所以，它有效。不过好像有点丑。

我的问题是:
对于此选择还有其他解决方案以及如何提高其生产力吗？

Answer 1

嘿，在您的情况下尝试一下这个表的旋转 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;