mysql - 模拟 row_number 函数

Question

我有 X 个类别、Y 个论坛和 Z 个主题。

• 主题属于一个论坛

• 论坛属于一个类别

• 我希望能够选择 X 类别和

  • 为每个类别选择前 3 个论坛和
  • 为每个论坛选择前 4 个话题。

(数字仅作为示例)

我通过变量模拟 row_numbers 来做到这一点，因为 mysql 不支持开箱即用。

不幸的是，行号仍然有问题。也许有人可以看看这里出了什么问题。为了更好的可读性，我在此处上传了带有代码突出显示的查询和结果

SELECT
    CatRow,
    c_id,
    c_name,
    ForumRow,
    f_id,
    f_name,
    ThreadRow,
    t_id,
    t_title
FROM (
    SELECT
        @cat_row    := IF(@prev_cat    = c.id, @cat_row+1, 1)   AS CatRow,
        @forum_row  := IF(@prev_forum  = f.id, @forum_row+1, 1) AS ForumRow,
        @thread_row := IF(@prev_thread = t.id, @thread_row+1, 1)AS ThreadRow,
        c.id        AS c_id,
        c.name      AS c_name,
        f.id        AS f_id,
        f.name      AS f_name,
        t.id        AS t_id,
        t.title     AS t_title,
        @prev_cat   := c.id,
        @prev_forum := f.id,
        @prev_thread:= t.id
    FROM (
        SELECT
            *
        FROM
            forum_categories c,
            (SELECT @cat_row := 1) AS x,
            (SELECT @prev_cat := '') AS y
        ORDER BY @cat_row
    ) AS c

    LEFT JOIN (
        SELECT
            *
        FROM
            forum_forums AS f,
            (SELECT @forum_row := 1) AS x,
            (SELECT @prev_forum := '') AS y
        ORDER BY @forum_row
    ) AS f ON (c.id = f.fk_forum_category_id )

    LEFT JOIN (
        SELECT
            *
        FROM
            forum_threads AS t,
            (SELECT @thread_row := 1) AS x,
            (SELECT @prev_thread := '') As y
        ORDER BY @thread_row
    ) AS t ON (f.id = t.fk_forum_forums_id )

    ORDER BY c.id ASC, f.id ASC, t.id ASC
) c
-- This is for later to actually limit the joins
-- WHERE CatRow <= 3 AND
-- ForumRow <= 3 AND
-- ThreadRow <= 4

结果如下:

    CatRow  c_id    c_name  ForumRow f_id   f_name      ThreadRow   t_id    t_title
4   1   General 4   2   Talk            1   42  talk
5   1   General 5   2   Talk            1   43  Talk...
6   1   General 6   2   Talk            1   44  locked thread
7   1   General 7   2   Talk            1   45  closed thread
3   1   General 3   2   Talk            1   48  :(:red::confuse::)
1   1   General 1   2   Talk            1   50  gsfdgsdg
2   1   General 2   2   Talk            1   51  asdasd
9   1   General 2   5   Voting          1   47  some title
8   1   General 1   5   Voting          1   49  sadfsad
1   2   Support 1   3   Help            1   40  Hueeelefe
2   2   Support 1   4   Features and Bugs   1   41  What is a bug?
3   2   Support 1   7   Test            1   NULL    NULL    
2   3   News    2   1   News            1   39  News by admin
1   3   News    1   1   News            1   46  further news

最后我需要能够指定:

• WHERE CatRow <= 9 AND
• 论坛行 <= 3
• AND 线程行 <= 4

由于 row_numbers 错误，这还不可能。有什么想法吗？？？

实际上我希望结果看起来像这样:

    CatRow  c_id    c_name  ForumRow f_id   f_name      ThreadRow   t_id    t_title
1   1   General 1   2   Talk            1   42  talk
1   1   General 1   2   Talk            2   43  Talk...
1   1   General 1   2   Talk            3   44  locked thread
1   1   General 1   2   Talk            4   45  closed thread
1   1   General 1   2   Talk            5   48  :(:red::confuse::)
1   1   General 1   2   Talk            6   50  gsfdgsdg
1   1   General 1   2   Talk            7   51  asdasd
1   1   General 2   5   Voting          1   47  some title
1   1   General 2   5   Voting          2   49  sadfsad
2   2   Support 1   3   Help            1   40  Hueeelefe
2   2   Support 2   4   Features and Bugs   1   41  What is a bug?
2   2   Support 3   7   Test            1   NULL    NULL    
3   3   News    1   1   News            1   39  News by admin
3   3   News    1   1   News            2   46  further news

为了更好的可读性，我上传了带有代码高亮的查询和结果图片: http://i.stack.imgur.com/9tzmH.png http://i.stack.imgur.com/xXF6U.png

Answer 1

SQL Fiddle

set @c_row := 0, @f_row := 0, @t_row := 0;
set @cat := 0, @forum := 0;

select CatRow, c_id, c_name, ForumRow, f_id, f_name, ThreadRow, t_id, title
from (
    select
        @c_row := if(@cat = c_id, @c_row, @c_row + 1) CatRow,
        @f_row := if(@forum = f_id, @f_row, if(@cat = c_id, @f_row + 1, 1)) ForumRow,
        @t_row := if(@forum = f_id, @t_row + 1, 1) ThreadRow,
        @cat := c_id as a,
        @forum := f_id as b,
        c_id,
        c_name,
        f_id,
        f_name,
        t_id,
        title
    from (
        select 
            c.id c_id, f.id f_id, t.id t_id,
            c.name c_name, f.name f_name, title
        from 
            forum_categories c
            inner join
            forum_forums f on f.c_id = c.id
            inner join
            forum_threads t on t.f_id = f.id
        order by 
            c.id, f.id, t.id
    ) s
) s;
+--------+------+---------+----------+------+-------------------+-----------+------+--------------------+
| CatRow | c_id | c_name  | ForumRow | f_id | f_name            | ThreadRow | t_id | title              |
+--------+------+---------+----------+------+-------------------+-----------+------+--------------------+
|      1 |    1 | General |        1 |    2 | Talk              |         1 |   42 | talk               |
|      1 |    1 | General |        1 |    2 | Talk              |         2 |   43 | Talk...            |
|      1 |    1 | General |        1 |    2 | Talk              |         3 |   44 | locked thread      |
|      1 |    1 | General |        1 |    2 | Talk              |         4 |   45 | closed thread      |
|      1 |    1 | General |        1 |    2 | Talk              |         5 |   48 | :(:red::confuse::) |
|      1 |    1 | General |        1 |    2 | Talk              |         6 |   50 | gsfdgsdg           |
|      1 |    1 | General |        1 |    2 | Talk              |         7 |   51 | asdasd             |
|      1 |    1 | General |        2 |    5 | Voting            |         1 |   47 | some title         |
|      1 |    1 | General |        2 |    5 | Voting            |         2 |   49 | sadfsad            |
|      2 |    2 | Support |        1 |    3 | Help              |         1 |   40 | Hueeelefe          |
|      2 |    2 | Support |        2 |    4 | Features and Bugs |         1 |   41 | What is a bug?     |
|      3 |    3 | News    |        1 |    1 | News              |         1 |   39 | News by admin      |
|      3 |    3 | News    |        1 |    1 | News              |         2 |   46 | further news       |
+--------+------+---------+----------+------+-------------------+-----------+------+--------------------+