首先，您只需要修复语法不正确的 JOIN(通常，WHERE 子句在所有 JOIN 之后，我们更喜欢显式连接而不是隐式连接).

To start with, yo would just need to fix your JOIN, whose syntax is not correct (typically, the WHERE clause goes after all JOINs, and we prefer explicit joins over implicit joins).

此外，正如 Madhur Bhaiya 所评论的那样，使用带有递增变量的过滤器很棘手.为了使其正常工作，我们需要在子查询中连接两个表，并在子查询本身内部进行排序，然后继续进行变量逻辑.

Also, as commented by Madhur Bhaiya, using filters with incremented variables is tricky. For this to work properly, we would need to join the two tables in a subquery, and do the ordering inside the subquery itself, and then proceed with the variable logic.

考虑:

SELECT id, type, timestamp, text  
FROM (
SELECT 
    t.*,
    @rn := CASE WHEN @type = type THEN @rn + 1 ELSE 1 END rn,
    @type := type
FROM 
    (
        SELECT t.*, t2.text,t2.id2
        FROM mytable t
        INNER JOIN mytable2 t2 ON t2.id2=t.id AND status=1
        ORDER BY type, timestamp
    ) t
    CROSS JOIN (SELECT @type := NULL, @rn := 1) x
) x
ORDER BY FLOOR((rn - 1)/3), type, timestamp;

DB Fiddle 演示:

| id  | type | timestamp | text |
| --- | ---- | --------- | ---- |
| 1   | A    | 101       | x    |
| 2   | A    | 102       | x    |
| 5   | A    | 105       | x    |
| 3   | B    | 103       | y    |
| 4   | B    | 104       | y    |
| 6   | B    | 106       | y    |
| 7   | A    | 107       | x    |
| 8   | A    | 108       | x    |
| 9   | B    | 109       | y    |
| 11  | B    | 111       | y    |