如何在 MySQL 中的两个表中的三个表中显示行 [英] How to show rows in packs of three of two tables in MySQL

问题描述

这是How-to-show-rows-in-packs-of-three-in-mysql的后续问题在 MySQL 中以三个为一组显示行

我有两张桌子:

mytable

ID  Type  Timestamp
1   A     101   
2   A     102
3   B     103
4   B     104   
5   A     105   
6   B     106   
7   A     107
8   A     108
9   B     109   
10  A     110   
11  B     111
12  B     112


mytable 2

ID2  Text  Status
1    x     1   
2    x     1
3    y     1
4    y     1   
5    x     1   
6    y     1   
7    x     1
8    x     1
9    y     1   
10   x     0   
11   y     1
12   y     0

我想显示按类型和时间戳排序的结果，其中类型每 3 行更改一次(忽略状态为 0 的行):

I want to show a result sorted by Type and Timestamp where every 3 rows the Type changes like this (rows with Status=0 are skipped):

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

我试过了:

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

ORDER BY 
FLOOR((rn - 1)/3),
type, 
timestamp;

[DB Fiddle 演示] (https://www.db-fiddle.com/f/7urWNPqXyGQ5ANVqxkjD7q/1)

[Demo on DB Fiddle] (https://www.db-fiddle.com/f/7urWNPqXyGQ5ANVqxkjD7q/1)

推荐答案

首先，您只需要修复语法不正确的 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    |

这篇关于如何在 MySQL 中的两个表中的三个表中显示行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！