dongmoyu0336
dongmoyu0336
2014-04-11 11:05

如何在mysql for Wordpress数据库中将行值转换为列标题

Using the following query I have fetched results from wp_posts and wp_postmeta table.

SELECT a., b. FROM wp_posts a, wp_postmeta b WHERE a.ID = b.post_id AND a.post_status = 'publish' AND a.post_type='wpsc-product'

From the above result, I want to filter the results to one more stage

As you know wp_postmeta structure is like

meta_id | post_id | meta_key | meta_value

I want to make all the meta_key values of the result to column names and its corresponding value as its value.

For eg if the table is like:

Meta_id-----------post_id-----------meta_key-----------meta_value

1-----------------31--------------mk1----------------mv1

2-----------------31--------------mk2----------------mv2

3-----------------31--------------mk3----------------mv3

The expected result is:

Meta_id-----------post_id-----------mk1--------mk2---------mk3

1-----------------31-----------------mv1---------mv2---------mv3

Is this possible??? I want this along with my join query.

The formatting of question is not correct. Please forgive for that.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • douzhe3516 douzhe3516 7年前

    You can use CASE if there are limited meta_keys

    SELECT a.*,b.Meta_id, b.post_id,
    CASE WHEN b.meta_key ='mk1' THEN b.meta_value END `mk1`,
    CASE WHEN b.meta_key ='mk2' THEN b.meta_value END `mk2`,
    CASE WHEN b.meta_key ='mk3' THEN b.meta_value END `mk3`
    FROM wp_posts a
    JOIN wp_postmeta b ON( a.ID = b.post_id )
    WHERE a.post_status = 'publish' 
    AND a.post_type='wpsc-product'
    AND b.meta_key IN('mk1','mk2','mk3')
    

    Edit for duplicates issue you can use JOINs

    SELECT p.*,
    a.Meta_id, 
    a.post_id,
    a.meta_value mk1, 
    b.meta_value mk2,
    c.meta_value mk3
    FROM wp_posts p  
    LEFT JOIN  wp_postmeta a ON(p.ID =a.post_id)
    JOIN  wp_postmeta b USING(post_id) /* is equal to ON(b.post_id = a.post_id) */
    JOIN  wp_postmeta c USING(post_id)
    WHERE a.meta_key = 'mk1'
    AND b.meta_key = 'mk2'
    AND c.meta_key='mk3'
    

    Fiddle Demo only for meta table

    点赞 评论 复制链接分享
  • douyan2970 douyan2970 7年前
    SELECT Meta_id,post_id,
      MAX(case when meta_key= 'mk1' then meta_value end) As mk1,
      MAX(case when meta_key= 'mk2' then meta_value end) As mk2,
      MAX(case when meta_key= 'mk3' then meta_value end) As mk3,
      MAX(case when meta_key= 'mk4' then meta_value end) As mk4
    
    FROM wp_postmeta 
    GROUP BY Meta_id,post_id
    

    If meta_value column values are not fixed , then search for MySQL implemenation of PIVOT feature as found in SQL Server.

    点赞 评论 复制链接分享

为你推荐