MySQL - 内连接和分组依据

Question

尝试连接两个表并创建报告:

wp_posts;
+----+-----------+
| id | post_name |
+----+-----------+
|  1 | Retailer1 | 
|  2 | Retailer2 | 
+----+-----------+

wp_postmeta;
+---------+-----------------+----------------+
| post_id | meta_key        | meta_value     |
+---------+-----------------+----------------+
|  1      | street_address  | 123 Main St.   | 
|  1      | city            | San Fran       | 
|  1      | province_state  | CA             | 
|  2      | street_address  | 321 Broadway   | 
|  2      | city            | New York       | 
|  2      | province_state  | NY             | 
+---------+-----------------+----------------+

我使用以下 SQL 语句:

SELECT 
     p.id
    , p.post_name
    ,(IF (pa.meta_key = 'street_address', pa.meta_value, NULL)) as `Address`
    ,(IF (pa.meta_key = 'city', pa.meta_value, NULL)) as `City`
    ,(IF (pa.meta_key = 'province_state', pa.meta_value, NULL)) as `Prov/State`
FROM wp_postmeta pa
left JOIN wp_posts AS p ON pa.post_id = p.id
where (p.post_status = 'publish' and
pa.meta_key = 'street_address')
or (p.post_status = 'publish' and
pa.meta_key = 'city')

现在我得到以下结果:

+-----+-------------+---------------+-------------+--------------+
|  id | post_name   | Address       |  City       |  Prov/State  |
+-----+-------------+---------------+-------------+--------------+
|  1  | Retailer1   | 123 Main St.  | NULL        |  NULL        |
|  1  | Retailer2   | 321 Broadway  | NULL        |  NULL        |
|  1  | Retailer1   | NULL          | SanFran     |  NULL        |
|  2  | Retailer2   | NULL          | New York    |  NULL        |
|  2  | Retailer1   | NULL          | NULL        | CA           |
|  2  | Retailer2   | NULL          | NULL        | NY           |
+-----+-------------+---------------+----------------------------+

有什么方法可以将所有这些值放入一个结果集中吗？我对 SQL 很烂，但似乎很接近，但仍然没有雪茄。

有什么想法吗？请帮忙!!!

Answer 1

是的，您想要进行聚合:

SELECT 
     p.id
    , p.post_name
    ,max(IF (pa.meta_key = 'street_address', pa.meta_value, NULL)) as `Address`
    ,max(IF (pa.meta_key = 'city', pa.meta_value, NULL)) as `City`
    ,max(IF (pa.meta_key = 'province_state', pa.meta_value, NULL)) as `Prov/State`
FROM wp_postmeta pa
left JOIN wp_posts AS p ON pa.post_id = p.id
where (p.post_status = 'publish' and
pa.meta_key = 'street_address')
or (p.post_status = 'publish' and
pa.meta_key = 'city')
group by pid, p.post_name;