使用mysql在2 meta_key中创建

Question

Assums table mysql from wordpress like this:

post_id | meta_key     | meta_value
19      | poster_url   | http://exampleimage.com
19      | vote_average | 7.5

i've tried using

 SELECT * FROM (Select ID,post_date,post_date_gmt,post_content,post_title,post_status,post_name,post_type,meta_key,meta_value as picture from get_movies where meta_key='poster_url' and post_status='publish' and post_type='post') as a,
(Select meta_value as rate from get_movies where meta_key='vote_average' and post_status='publish' and post_type='post') as b

but result has duplicated and miss for 'rate' .. in this case , i just need for make alias each other.. like poster_url = picture, and vote_average = rate

it's possible?

Answer 1

For some reason, I think this may be what you want:

Select ID, post_date, post_date_gmt, post_content, post_title,
       post_status, post_name, post_type,
       max(case when meta_key = 'poster_url' then meta_value end) as picture,
       max(case when meta_key = 'vote_average' then meta_value end) as rating
from get_movies
where post_status = 'publish' and post_type = 'post' and
      meta_key in ('poster_url', 'vote_average') 
group by ID, post_date, post_date_gmt, post_content, post_title,
         post_status, post_name, post_type;

It returns the meta key values in two different columns.