MySQL Select, Join, and Set a key as Column Name——SQL AS

Question

这是 SQLFiddle: http://sqlfiddle.com/#!2/b2cf7/15

我想连接两个表，并选择 meta_key 并将其作为 meta_value 字段的列名。这是我要加入的表

表一

+----+-------------+-----------------------+
| ID | post_author |       post_date       |
+----+-------------+-----------------------+
| 20 |           1 | '2014-02-13 22:29:04' |
| 21 |           1 | '2014-02-13 22:29:04' |
| 22 |           2 | '2014-02-13 22:29:04' |
| 23 |           1 | '2014-02-13 22:29:04' |
| 24 |           2 | '2014-02-13 22:29:04' |
+----+-------------+-----------------------+

表二

+---------+---------+---------------+----------------------------------------+
| meta_id | post_id |   meta_key    |               meta_value               |
+---------+---------+---------------+----------------------------------------+
|      42 |      20 | 'user_title'  | 'My Title!'                            |
|      43 |      20 | 'user_upload' | 'true'                                 |
|      44 |      20 | 'user_desc'   | 'a description!'                       |
|      46 |      21 | 'user_title'  | 'whats that about!'                    |
|      47 |      21 | 'user_upload' | 'truth'                                |
|      48 |      21 | 'user_desc'   | 'this table runs'                      |
|      49 |      22 | 'user_title'  | 'third title, dang!'                   |
|      50 |      22 | 'user_upload' | 'true'                                 |
|      51 |      22 | 'user_desc'   | 'the desc is always true in this case' |
|      52 |      23 | 'user_title'  | 'another one'                          |
|      53 |      23 | 'user_upload' | 'true')                                |
|      54 |      23 | 'user_desc'   | 'thrilling'                            |
|      55 |      23 | 'user_title'  | 'lasty last'                           |
|      57 |      23 | 'user_upload' | 'true'                                 |
|      59 |      23 | 'user_desc'   | 'and its done'                         |
+---------+---------+---------------+----------------------------------------+

这就是我希望它处理 JOIN 和 SELECT AS 的方式

+---------+-------------+----------------------+-------------+----------------------------------------+
| post_id | post_author |      user_title      | user_upload |               user_desc                |
+---------+-------------+----------------------+-------------+----------------------------------------+
|      20 |           1 | 'My Title!'          | 'true'      | 'a description!'                       |
|      21 |           1 | 'whats that about!'  | 'truth'     | 'this table runs'                      |
|      22 |           2 | 'third title, dang!' | 'true'      | 'the desc is always true in this case' |
|      23 |           1 | 'another one'        | 'true'      | 'thrilling'                            |
|      24 |           2 | 'lasty last'         | 'true'      | 'and its done'                         |
+---------+-------------+----------------------+-------------+----------------------------------------+

Answer 1

解决此问题的一种方法是使用 join 和条件聚合:

select p.id as post_id, p.post_author,
       max(case when pm.meta_key = 'user_title' then pm.meta_value end) as user_title,
       max(case when pm.meta_key = 'user_upload' then pm.meta_value end) as user_upload,
       max(case when pm.meta_key = 'user_desc' then pm.meta_value end) as user_desc
from posts p left outer join
     postmeta pm
     on p.id = pm.post_id
group by p.id, p.post_author;

另一种方法是使用连接:

select p.id as post_id, p.post_author, title.meta_value as user_title,
       upload.meta_value as user_upload, desc.meta_value as user_desc
from posts p left outer join
     postmeta title
     on p.id = title.post_id and title.meta_value = 'user_title' left outer join
     postmeta upload
     on p.id = title.post_id and title.meta_value = 'user_upload' left outer join
     postmeta desc
     on p.id = title.post_id and title.meta_value = 'user_desc';

哪个更好取决于几个因素，例如数据的大小、索引以及表的大小。

编辑:

要添加where upload = 'true'，您可以添加:

having user_upload = 'true'

任一查询。这首先是因为 group by。它适用于第二个，因为 MySQL 扩展允许在 having 子句中使用列别名进行过滤，即使没有 group by。