mysql - 从 Wordpress 数据库中的单个列中选择多个值

Question

我正在尝试输出帖子详细信息，以及 wordpress 数据库中的一些自定义字段。我有名为“缩略图”和“视频”的自定义字段

SELECT ID, post_title, post_content, wp_postmeta.meta_value
FROM wp_posts
INNER JOIN wp_postmeta ON wp_posts.id=wp_postmeta.post_id
WHERE wp_postmeta.meta_key = "thumnail"
OR wp_postmeta.meta_key = "video"

到目前为止，我得到的是这样的单独行:

| ID  | Title     | Content     | Custom Fields                      |
|--------------------------------------------------------------------|
| 234 | INCEPTION | Content etc | wp-content/thumbnail/inception.jpg |
| 234 | INCEPTION | Content etc | wp-content/video/inception.flv     |

但我想要的是(即每篇文章一行)

|ID |Title     |Content     |Thumbnail                  |Video                      |
|-----------------------------------------------------------------------------------|
|234|INCEPTION |Content etc |wp-content/..inception.jpg |wp-content/..inception.flv |

有人可以建议如何将不同的值分隔到 SELECT 语句中的列中吗？

Answer 1

此过程称为“数据透视表”或“交叉表报告”。 MySQL 中没有 PIVOT 命令，但可以手动完成，这里有两个例子。第一个示例使用 IF statement ，第二个CASE statement .还有一个 aggregate function用于去除重复的行。

SELECT ID, post_title, post_content,
MAX( IF ( wp_postmeta.meta_key = "thumnail", wp_postmeta.meta_value, '' ) ) AS Thumbnail,
MAX( IF ( wp_postmeta.meta_key = "video", wp_postmeta.meta_value, '' ) ) AS Video
FROM wp_posts
INNER JOIN wp_postmeta ON wp_posts.id=wp_postmeta.post_id
WHERE wp_postmeta.meta_key IN ( "thumnail", "video" )
GROUP BY ID

SELECT ID, post_title, post_content,
MAX( CASE WHEN wp_postmeta.meta_key = "thumnail" THEN wp_postmeta.meta_value ELSE '' END ) AS Thumbnail,
MAX( CASE WHEN wp_postmeta.meta_key = "video" THEN wp_postmeta.meta_value ELSE '' END ) AS Video
FROM wp_posts
INNER JOIN wp_postmeta ON wp_posts.id=wp_postmeta.post_id
WHERE wp_postmeta.meta_key IN( "thumnail", "video" )
GROUP BY ID