如何在内部连接mysql上订购数据？

我有一个案例来创建基于类别排序的播客播放列表。例如，我想根据下表连接podcast数据和category数据。

> SELECT * FROM category_sorts;

+----+-------------+------+
| id | category_id | sort |
+----+-------------+------+
|  1 |           2 |    1 |
|  2 |           1 |    2 |
|  3 |           1 |    3 |
|  4 |           2 |    4 |
+----+-------------+------+

列 category_id 与类别表相关。

> SELECT * FROM categories;

+----+--------------+-----------+
| id | title        | status    |
+----+--------------+-----------+
|  1 | Saga         | published |
|  2 | Asia Calling | published |
+----+--------------+-----------+

这里有一些来自 podcasts 具有相关类别的表。

> SELECT * FROM podcasts;

+----+-------------+------------------------+---------------------+
| id | category_id | title                  | published_at        |
+----+-------------+------------------------+---------------------+
|  1 |           1 | Sejarah Rumah Dengklok | 2018-05-01 22:40:47 |
|  3 |           2 | Berita Asia!           | 2018-05-01 22:52:15 |
|  4 |           2 | Asia Later!            | 2018-05-01 23:47:13 |
|  5 |           1 | Saga terbaru           | 2018-05-02 00:06:32 |
+----+-------------+------------------------+---------------------+

使用下面提供的查询，我得到了相同类别的重复播客。

SELECT
        p.id, p.title AS podcast_title, p.published_at, 
        c.title AS category_title,
        cs.sort
    FROM podcasts as p
    INNER JOIN categories as c ON c.id = p.category_id
    INNER JOIN category_sorts AS cs on cs.category_id = c.id
    GROUP BY cs.sort
    ORDER BY cs.sort ASC, p.published_at DESC

结果：

+----+------------------------+---------------------+----------------+------+
| id | podcast_title          | published_at        | category_title | sort |
+----+------------------------+---------------------+----------------+------+
|  3 | Berita Asia!           | 2018-05-01 22:52:15 | Asia Calling   |    1 |
|  1 | Sejarah Rumah Dengklok | 2018-05-01 22:40:47 | Saga           |    2 |
|  1 | Sejarah Rumah Dengklok | 2018-05-01 22:40:47 | Saga           |    3 |
|  3 | Berita Asia!           | 2018-05-01 22:52:15 | Asia Calling   |    4 |
+----+------------------------+---------------------+----------------+------+

我期待什么？
我想订购播放列表基于 sort 专栏和最新播客 published_at 列。订单使用 sort 列可以，但我不能按顺序 published_at 播客上的日期。
预期结果：

+----+------------------------+---------------------+----------------+------+
| id | podcast_title          | published_at        | category_title | sort |
+----+------------------------+---------------------+----------------+------+
|  4 | Asia Later!            | 2018-05-01 22:52:15 | Asia Calling   |    1 |
|  5 | Saga Terbaru           | 2018-05-01 22:40:47 | Saga           |    2 |
|  1 | Sejarah Rumah Dengklok | 2018-05-01 22:40:47 | Saga           |    3 |
|  3 | Berita Asia!           | 2018-05-01 22:52:15 | Asia Calling   |    4 |
+----+------------------------+---------------------+----------------+------+

有人能告诉我怎么处理吗？任何链接或来源将不胜感激。
如果您想使用SQLFiddle，下面是一个完整的表结构和数据示例。
谢谢您。

如果您不需要多个类别，请在您的帖子下查看我的评论，但如果您希望允许多个类别，如示例所示，请尝试下面的sql。唯一的变化是它按类别\u id分组，这样您就不会得到重复项：

SELECT
        p.id, p.title AS podcast_title, p.published_at, 
        c.title AS category_title,
        cs.sort
    FROM podcasts as p
    INNER JOIN categories as c ON c.id = p.category_id
    INNER JOIN category_sorts AS cs on cs.category_id = c.id
    GROUP BY cs.category_id
    ORDER BY cs.sort ASC, p.published_at DESC

或者让它更防弹 min(cs.sort) as sorting_order 因此，订单总是被正确地挑选出来并用于 ORDER 条款：

SELECT
        p.id, p.title AS podcast_title, p.published_at, 
        c.title AS category_title,
        min(cs.sort) as sorting_order
    FROM podcasts as p
    INNER JOIN categories as c ON c.id = p.category_id
    INNER JOIN category_sorts AS cs on cs.category_id = c.id
    GROUP BY cs.category_id
    ORDER BY sorting_order ASC, p.published_at DESC

但是，如果你想保留类别的重复项并去掉播客的重复项，那么：

SELECT
        p.id, p.title AS podcast_title, p.published_at, 
        c.title AS category_title,
        cs.sort
    FROM podcasts as p
    INNER JOIN categories as c ON c.id = p.category_id
    INNER JOIN category_sorts AS cs on cs.category_id = c.id
    GROUP BY p.id
    ORDER BY cs.sort ASC, p.published_at DESC

在这里摆弄：http://sqlfiddle.com/#!9/3672ce/22号

如何在内部连接mysql上订购数据？

1条答案

相关问题

热门标签

最新问答