mysql - 如何在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;