如何更好地在MySQL查询中获得不同的行？

Question

我的查询得到了我所需要的所有信息，但对于我的生活，我似乎无法得到它来按照我想要的方式组织行。很明显，这些信息来自两个不同的表，其中一个是有条理的，所以我想要的那一天不是以一种容易获得的方式组织起来的，如下所示：

post_id | meta_key | meta_value
--------|----------|-----------
1       | key_one  | value1
1       | key_two  | value2
2       | key_one  | value3
2       | key_two  | value4

当我想：

post_id | key_one | key_two
--------|----------|-----------
1       | value1   | value2
2       | value3   | value4

这是查询，返回值在此下面。同样，正确的信息，但分散在各行。我知道我只是一个陈述..。

SELECT DISTINCT wp_postmeta.post_id, 
CASE WHEN wp_postmeta.meta_key = 'allowed' THEN wp_postmeta.meta_value END allowed,
CASE WHEN wp_postmeta.meta_key = 'amenities' THEN wp_postmeta.meta_value END amenities,
CASE WHEN wp_postmeta.meta_key = 'trail-type' THEN wp_postmeta.meta_value END trailtype,
CASE WHEN wp_postmeta.meta_key = 'weatherlat' THEN wp_postmeta.meta_value END weatherlat,
CASE WHEN wp_postmeta.meta_key = 'weatherlon' THEN wp_postmeta.meta_value END weatherlon
from wp_postmeta WHERE wp_postmeta.post_id IN (SELECT object_id AS id FROM wp_term_relationships WHERE term_taxonomy_id = 74)

下面是我的回应，下面是我想得到的

post_id | allowed | amenities | trailtype | weatherlat | weatherlon
--------|---------|-----------|-----------|------------|-----------
503     | 1,2,3   | NULL      | NULL      | NULL       | NULL
503     | NULL    | No-Water  | NULL      | NULL       | NULL
503     | NULL    | NULL      | Easy      | NULL       | NULL
503     | NULL    | NULL      | NULL      | NULL       | NULL
503     | NULL    | NULL      | NULL      | 47.37070   | NULL
503     | NULL    | NULL      | NULL      | NULL       | -122.00633
566     | 5,6,7   | NULL      | NULL      | NULL       | NULL
566     | NULL    | Restrooms | NULL      | NULL       | NULL
566     | NULL    | NULL      | Moderate  | NULL       | NULL
566     | NULL    | NULL      | NULL      | NULL       | NULL
566     | NULL    | NULL      | NULL      | 47.729212  | NULL
566     | NULL    | NULL      | NULL      | NULL       | -117.142829

我想要的

post_id | allowed | amenities | trailtype | weatherlat | weatherlon
--------|---------|-----------|-----------|------------|-----------
503     | 1,2,3   | No-Water  | Easy      | 47.37070   | -122.00633
566     | 5,6,7   | Restrooms | Moderate  | 47.729212  | -117.142829

Answer 1

修改查询以在每一列上使用group by和聚合函数：

SELECT wp_postmeta.post_id, 
       max(CASE WHEN wp_postmeta.meta_key = 'allowed' THEN wp_postmeta.meta_value END) as allowed,
       max(CASE WHEN wp_postmeta.meta_key = 'amenities' THEN wp_postmeta.meta_value END) as amenities,
       max(CASE WHEN wp_postmeta.meta_key = 'trail-type' THEN wp_postmeta.meta_value END) as trailtype,
       max(CASE WHEN wp_postmeta.meta_key = 'weatherlat' THEN wp_postmeta.meta_value END) as weatherlat,
       max(CASE WHEN wp_postmeta.meta_key = 'weatherlon' THEN wp_postmeta.meta_value END) as weatherlon
from wp_postmeta
where wp_postmeta.post_id IN (SELECT object_id AS id
                              FROM wp_term_relationships
                              WHERE term_taxonomy_id = 74
                             )
group by wp_postmeta.post_id;