mysql，选择具有相同id但另一列中不同值的两个不同行

Question

先发制人地为标题道歉......不知道该写什么......

我对 mysql 不是最好的，而且我有一个我无法解决的问题。

我有一张表....这是 WordPress 站点的元数据表...在表中，我存储了 FB 之类的计数和每个帖子的永久链接......所以一篇帖子看起来像这样......

+-------------+----------------+-----------------+----------------------------------+| meta_id     |    post_id     |     meta_key    |            meta_value            |+-------------+----------------+-----------------+----------------------------------+|           1 |      446       |    _fb_count    |                 2                ||           2 |      446       |    _permalink   | /2013/08/image-aligned-left/446/ |+-------------+----------------+-----------------+----------------------------------+

What I would like to do is query this table and return both these values so that the returned data would be (post_id,Facebook Like Count, Permalink)... So it would look like:

+----------------+-----------------+----------------------------------+|    post_id     |     _fb_count   |            _permalink            |+----------------+-----------------+----------------------------------+|      446       |        2        | /2013/08/image-aligned-left/446/ |+----------------+-----------------+----------------------------------+

I looked around and am guessing I need to do some kind of inner join on the same table.... but I am not getting the results I want...

I tried something like this with no success:

SELECT pl.post_id,pl.meta_value,FBL.meta_value from wp_postmeta pl inner join wp_postmeta FBL on pl.post_id = FBL.post_id WHERE pl.meta_key = '_permalink' OR FBL.meta_key = '_fb_count

如有任何帮助，我们将不胜感激...

Answer 1

您必须为此使用条件聚合

SELECT post_id,
       MAX(CASE WHEN meta_key = '_fb_count' THEN meta_value END) `_fb_count`,
       MAX(CASE WHEN meta_key = '_permalink' THEN meta_value END) `_permalink`
  FROM wp_postmeta
 WHERE meta_key IN('_fb_count', '_permalink')
 GROUP BY post_id

输出:

| POST_ID | _FB_COUNT |                       _PERMALINK ||---------|-----------|----------------------------------||     446 |         2 | /2013/08/image-aligned-left/446/ |

这里是SQLFiddle 演示

但我同意@Rob 的观点，你可能最好使用 WP 的适当函数来实现这一点。