复杂的MySQL查询

Question

我正在创建一个WordPress网站，用于存储有关电视节目的信息。我使用自定义字段来选择每个帖子。

表格大致如下

+----+---------+----------+------------+
| id | post_id | meta_key | meta_value |
+----+---------+----------+------------+
| 1  |    1    |   name   | Smallville |
| 2  |    1    |  season  |     1      |
| 3  |    1    |  episode |     1      |
| 4  |    2    |   name   | Smallville |
| 5  |    2    |  season  |     1      |
| 6  |    2    |  episode |     2      |
+----+---------+----------+------------+

基本上，我需要做的是选择所有名称为“Smallville”的电视节目，并按季度和剧集排序。我认为这应该很简单，但我尝试过的所有方法都没有返回任何结果。请问如何实现这个功能？

- Ben Shelock

---

你是否有控制数据库并将其重新设计成更规范的格式？或者这是不可能的事情？ - dustmachine

为什么你要使用这样的表格格式？ - Noah Goodrich

很遗憾不是Dustmachine，我正在使用WordPress。 - Ben Shelock

Answer 1

你可以这样做：

SELECT 
    t1.post_id, 
    t1.meta_value AS name, 
    t2.meta_value AS season, 
    t3.meta_value AS episode
FROM
    (
    SELECT *
    FROM the_table
    WHERE meta_key = 'name'
    ) t1
INNER JOIN
    (
    SELECT *
    FROM the_table
    WHERE meta_key = 'season'
    ) t2 ON t1.post_id = t2.post_id
INNER JOIN
    (
    SELECT *
    FROM the_table
    WHERE meta_key = 'episode'
    ) t3 ON t1.post_id = t3.post_id

这将给你结果：

| post_id | name       | season | episode |
-------------------------------------------
|    1    | Smallville | 1      | 1       |
|    2    | Smallville | 1      | 2       |

使用这种形式可以更方便地进行任何操作。

你需要添加：

WHERE name = 'Smallville'
ORDER BY season, episode

- Lukasz Lysik

---

@Lukasz - 你有一个小错误 - SELECT列表应该是：t1.post_id，t1.meta_value作为名称，t2.meta_value作为季节，t3.meta_value作为剧集。 - martin clayton

Answer 2

使用自连接合并行，就可以轻松完成:

SELECT      *
FROM        yourtable name
INNER JOIN  yourtable season 
            on season.post_id = name.post_id
            and season.meta_key = 'season'
INNER JOIN  yourtable episode
            on episode.post_id = name.post_id
            and episode.meta_key = 'episode'
WHERE       name.meta_key = 'name'
            and name.meta_value = 'Smallville'
ORDER BY    season.meta_value, episode.meta_value

- Andomar

---

干得好，Andomar。在这里将其转换为另一个示例： http://codex.wordpress.org/Function_Reference/wpdb_Class#SELECT_a_Column - Michael

Answer 3

更一般化的情况：从您的格式到更常规的关系型数据库格式的转换：

SELECT (SELECT meta_value FROM data t1 WHERE t1.post_id = t0.post_id AND meta_key = "season") AS season,
   (SELECT meta_value FROM data t1 WHERE t1.post_id = t0.post_id AND meta_key = "episode") AS episode
FROM data t0 WHERE meta_key = "name" AND meta_value = "Smallville"

在实际的排序中，您不能重新使用季节/集数值（这些值在排序时尚未分配），因此您必须将子查询复制/粘贴到ORDER BY子句中：

ORDER BY (SELECT ... "season") ASC, (SELECT ... "episode") ASC, 

- Wim

Answer 4

不需要直接使用SQL。您可以通过WP_Query对象访问SQL查询。查看WP_Query对象中的where子句周围的过滤器（有多种方法可用于获取它），并在它们被连接在一起之前简单地修改默认的WP_Query部分即可。

首先设置一个WP_Query对象，该对象通过postmeta键和postmeta值获取所有帖子，然后添加更多内容到where子句以执行一些额外的条件语句。

还有另一个过滤器，允许您获取SQL查询的排序部分，以便您可以修改它。

这里没有必要手写SQL，只需修改已经为您构建的内容即可。

- Gipetto

Answer 5

这个想法是将表格自身连接3次，其中每次都取一个给定meta_key的行：

SELECT t1.meta_value name, t2.meta_value season, t3.meta_value episode
FROM table t1
JOIN table t2 on t1.post_id = t2.post_id and t2.meta_key = 'season'
JOIN table t3 on t1.post_id = t3.post_id and t3.meta_key = 'episode'
WHERE t1.meta_key = 'name'
  AND t1.meta_value = 'Smallville'
ORDER BY t2.meta_value, t3.meta_value

- manji