mysql - 如果列中有一个相同的值并更改列名，如何将两行合并为一

Question

我有这个查询

SELECT
    pm.post_id,
    pm.meta_key,
    pm.meta_value
FROM 
   birr_postmeta as pm,
   birr_posts as p
WHERE pm.meta_key IN ('ratings_average', 'ratings_users')
    AND pm.meta_value <> 0
    AND pm.post_id = p.ID
ORDER BY pm.post_id ASC

这还给我这个：

我想从这些表中获得的是这样的

+--------+---------------+--------------+
|post_id |  rating_users |rating_average|
+--------+---------------+--------------+
|    7   |       1       |      5       |
|   35   |       3       |      4       |
|   101  |       2       |     4.5      |
|   127  |       2       |      4       |
|   225  |       1       |      4       |
+--------+---------------+------------+

我怎样才能做到这一点？

标签： mysqlsql

Answer 1

一种方法使用聚合：

SELECT pm.post_id,
       MAX(CASE WHEN pm.meta_key = 'ratings_average' THEN pm.meta_value END) as ratings_average,
       MAX(CASE WHEN pm.meta_key = 'ratings_users' THEN pm.meta_value END) as ratings_users
FROM  birr_postmeta pm 
WHERE pm.meta_key IN ('ratings_average', 'ratings_users')
      pm.meta_value <> 0
GROUP BY pm.post_id ASC;

请注意，没有JOIN是必要的。但是如果你要使用一个，你应该使用正确的JOIN语法。