Mysql从同一表中的同一行返回多列

Question

假设我有一个表名“POST”，它只包含“POST_ID”，我有另一个表名 post_meta，它也包含“POST_ID”(作为外键)和另外两个列“meta_key”和“meta_value”

我的表的结构是

张贴表:

post_ID
1
2

元表:

meta_id      post_id        meta_key          meta_value 
1              1            suite_size        1000
2              1            suite_number      10 
3              2            suite_size        2000
4              2            suite_number      20  

我想创建一个这样的表

post_id   suite_size  suite_number
1         1000         10
2         2000         20  

我该怎么做？

Answer 1

试试这个:

SELECT pm.post_ID,
       SUM(CASE WHEN pm.meta_key = 'suite_size' THEN pm.meta_value ELSE 0 END) as suite_size,
       SUM(CASE WHEN pm.meta_key = 'suite_number' THEN pm.meta_value ELSE 0 END) as suite_number
FROM post_meta pm 
GROUP BY pm.post_ID;

或

如果您想要所有帖子是否不存在于 post_meta 表中，请使用以下查询

SELECT p.post_ID,
       SUM(CASE WHEN pm.meta_key = 'suite_size' THEN pm.meta_value ELSE 0 END) as suite_size,
       SUM(CASE WHEN pm.meta_key = 'suite_number' THEN pm.meta_value ELSE 0 END) as suite_number
FROM post p
LEFT JOIN post_meta pm ON p.post_id = pm.post_ID
GROUP BY p.post_ID;