MySQL -如何先加入然后做WHERE然后限制偏移

为简单起见：有这三张table
my_tickets

+-----------+---------+
| ticket_id | user_id |
+-----------+---------+
| 186       | 2       |
| 187       | 2       |
| 188       | 2       |
| 253       | 33      |
| 254       | 33      |
| 256       | 33      |
| 261       | 33      |
| 262       | 33      |
| 263       | 33      |
| 1573      | 7       |
| 1597      | 7       |
| 1748      | 7       |
+-----------+---------+

my_users

+----+---------+
| id | name    |
+----+---------+
| 2  | user_2  |
| 7  | user_7  |
| 33 | user_33 |
+----+---------+

my_data

+----+-----------+------------+
| id | ticket_id | data       |
+----+-----------+------------+
| 1  | 186       | data_186_1 |
| 2  | 186       | data_186_2 |
| 3  | 187       | data_187_1 |
| 4  | 253       | data_253_1 |
| 5  | 253       | data_253_2 |
| 6  | 253       | data_253_3 |
| 7  | 254       | data_254_1 |
+----+-----------+------------+

我需要什么：在表被连接之后，获取3个票据的数据（类似于数据的对象，而不是行）&仅针对user_id=33。我将使用它进行分页。

Desired output
+-----------------------+---------------------+----------------+---------------+
| my_tickets__ticket_id | my_tickets__user_id | my_users__name | my_data__data |
+-----------------------+---------------------+----------------+---------------+
| 253                   | 33                  | user_33        | data_253_1    |
| 253                   | 33                  | user_33        | data_253_2    |
| 253                   | 33                  | user_33        | data_253_3    |
| 254                   | 33                  | user_33        | data_254_1    |
| 256                   | 33                  | user_33        | NULL          |
+-----------------------+---------------------+----------------+---------------+

请阅读此文章-https://dncrews.com/limit-and-offset-can-work-with-join-f03327fa2ad3
步骤：
Step 1: JOINmy_tickets.user_id <=> my_users.idmy_tickets.ticket_id <=> my_data.ticket_id)

+-----------------------+---------------------+----------------+---------------+
| my_tickets__ticket_id | my_tickets__user_id | my_users__name | my_data__data |
+-----------------------+---------------------+----------------+---------------+
| 186                   | 2                   | user_2         | data_186_1    |
| 186                   | 2                   | user_2         | data_186_2    |
| 187                   | 2                   | user_2         | data_187_1    |
| 188                   | 2                   | user_2         | NULL          |
| 1573                  | 7                   | user_7         | NULL          |
| 1597                  | 7                   | user_7         | NULL          |
| 1748                  | 7                   | user_7         | NULL          |
| 253                   | 33                  | user_33        | data_253_1    |
| 253                   | 33                  | user_33        | data_253_2    |
| 253                   | 33                  | user_33        | data_253_3    |
| 254                   | 33                  | user_33        | data_254_1    |
| 256                   | 33                  | user_33        | NULL          |
| 261                   | 33                  | user_33        | NULL          |
| 262                   | 33                  | user_33        | NULL          |
| 263                   | 33                  | user_33        | NULL          |
+-----------------------+---------------------+----------------+---------------+

Step 2: WHERE (my_tickets.user_id = 33)

+-----------------------+---------------------+----------------+---------------+
| my_tickets__ticket_id | my_tickets__user_id | my_users__name | my_data__data |
+-----------------------+---------------------+----------------+---------------+
| 253                   | 33                  | user_33        | data_253_1    |
| 253                   | 33                  | user_33        | data_253_2    |
| 253                   | 33                  | user_33        | data_253_3    |
| 254                   | 33                  | user_33        | data_254_1    |
| 256                   | 33                  | user_33        | NULL          |
| 261                   | 33                  | user_33        | NULL          |
| 262                   | 33                  | user_33        | NULL          |
| 263                   | 33                  | user_33        | NULL          |
+-----------------------+---------------------+----------------+---------------+

Step 3: LIMIT (limit the result to 3 tickets (object kind))

+-----------------------+---------------------+----------------+---------------+
| my_tickets__ticket_id | my_tickets__user_id | my_users__name | my_data__data |
+-----------------------+---------------------+----------------+---------------+
| 253                   | 33                  | user_33        | data_253_1    |
| 253                   | 33                  | user_33        | data_253_2    |
| 253                   | 33                  | user_33        | data_253_3    |
| 254                   | 33                  | user_33        | data_254_1    |
| 256                   | 33                  | user_33        | NULL          |
+-----------------------+---------------------+----------------+---------------+

我尝试了什么：

Query我期望这个查询在连接表之后输出3个结果（如步骤3所示）&仅针对user_id=33，但输出为空

SELECT * FROM (
    SELECT 
        my_tickets.ticket_id AS my_tickets__ticket_id, my_tickets.user_id AS my_tickets__user_id,
        my_users.name AS my_users__name, my_data.data AS my_data__data
    FROM my_tickets
    
    LEFT JOIN my_users ON my_tickets.user_id=my_users.id 
    LEFT JOIN my_data ON my_tickets.ticket_id=my_data.ticket_id 

    WHERE my_tickets.user_id = 33

) as t1

    WHERE
    
    t1.my_tickets__ticket_id IN (
        SELECT * FROM (
            SELECT ticket_id FROM my_tickets LIMIT 3 OFFSET 0
        ) as t2
    )

OUTPUT: Empty table

UPDATE：回复lemon的说法：我使用子查询使事情变得过于复杂是有原因的。我需要使用LIMIT OFFSET对内容进行分页。MySQL对返回的行数应用LIMIT，另一方面，我需要对类似对象的数据应用LIMIT。为什么对象像数据？=>我更新了初始问题中的表以反映这一点（添加了my_data表）基于本文-https://dncrews.com/limit-and-offset-can-work-with-join-f03327fa2ad3

你好像把事情弄得太复杂了。你可以不使用子查询：

SELECT t.ticket_id,
       t.user_id,
       u.name
FROM       mytickets t
INNER JOIN myusers u ON t.user_id = u.id
WHERE user_id = 33
LIMIT 3

这将保持正确的结果，因为以下内容按给定顺序激活：

FROM子句
WHERE子句
SELECT子句
LIMIT子句
这里你可以得到一个thorough discussion在子句之间的执行顺序，或者你可以去official documentation。
另外，请注意，输出不是确定性的，因为您不知道数据在数据库中存储的顺序。确定性地检索这三行的唯一方法是使用ORDER BY子句，该子句能够在行之间强加一个顺序并打破潜在的联系。

输出：

票证ID	用户ID	姓名
二百五十三	三十三	用户_33
二百五十四	三十三	用户_33
二百五十六	三十三	用户_33

查看演示here。

更新：如果我想限制类对象数据的数量，该怎么办？

不要使用LIMIT，因为它会带来不必要的复杂性，并且变得非常难以处理。
您可以对DENSE_RANK排名窗口函数的输出应用过滤。它将为类似对象的行的每条记录分配一个标识符，您可以在其上应用筛选。
下面是一个例子：

WITH cte AS (
    SELECT t.ticket_id,
           t.user_id,
           u.name,
           DENSE_RANK() OVER(ORDER BY user_id) AS rn
    FROM       mytickets t
    INNER JOIN myusers u ON t.user_id = u.id
)
SELECT * 
FROM cte
WHERE rn <= 3

这个查询将允许您从最后三个user_id值中挑选所有记录，而不仅仅是最后三个记录。

MySQL -如何先加入然后做WHERE然后限制偏移

2条答案

相关问题

热门标签

最新问答