复杂的MySQL查询 [英] Complicated MySQL Query

问题描述

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

I'm creating a site in wordpress which holds information on television programs. I'm using custom fields to select each post.

桌子看起来像这样

+----+---------+----------+------------+
| 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"的电视节目，然后按季节对它们进行分类，然后按情节进行分类.我以为那会很简单，但是我尝试过的一切都没有任何回报.

Basically what I need to do is select all of the tv shows with the name "Smallville" and sort them by season then by episodes. I thought it would be fairly simple but everything I have tried returns nothing.

您能解释一下我该怎么做吗?

Could you please explain how I can do this?

推荐答案

您可以执行以下操作:

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       |

采用这种形式，任何操作都容易得多.

In this form it is much easier for any operations.

您需要添加:

WHERE name = 'Smallville'
ORDER BY season, episode

这篇关于复杂的MySQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！