在 Mysql 中对子查询使用 LIMIT [英] Using LIMIT with Subqueries in Mysql

问题描述

在我的数据库中，一个线索可以有很多任务，因此任务表有一个 taskable_id 和 taskable_type，其中 taskable_id 是线索的 id，taskable_type 是Lead"，遵循 Ruby on Rails 多态约定.

In my database, a lead can have many tasks and therefore the the tasks table has a taskable_id and taskable_type, where taskable_id is id of lead and taskable_type is "Lead", following the Ruby on Rails polymorphic conventions.

我想为所有约会状态为 1 的潜在客户选择第一个任务.就是这样.结果集应该是任务的集合.这是我想出的:

I want to select the first task for all leads whose appointment status is 1. That's it. The result set should be a collection of tasks. This is what I came up with:

SELECT 
    `tasks`.* 
FROM 
    `tasks`  
      WHERE `tasks`.`taskable_id` IN (1, 2) 
      AND 
      `tasks`.`taskable_type` = 'Lead' 
      AND 
      `tasks`.`id` 
      IN 
        (
            SELECT  `tasks`.`id` 
            FROM `tasks`  
            WHERE `tasks`.`taskable_id` IN (1, 2) 
            AND 
            `tasks`.`taskable_type` = 'Lead' 
            AND 
            'appointment_status' = 1 
            ORDER BY created_at asc 
            LIMIT 1
        );   

但这会产生错误此版本的 MySQL 尚不支持‘LIMIT & IN/ALL/ANY/SOME 子查询".其他stackoverflow帖子说对于这个错误，LIMIT必须在子查询之外.

But this gives error "This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery". Other stackoverflow posts said that for this error, LIMIT must be outside subquery.

这是示例数据:

mysql> SELECT * FROM tasks;
+----+------+--------+-------------+----------+---------------------+---------------------+-----------+---------------------+---------------------+---------------+-------------+
| id | name | status | description | priority | created_at          | updated_at          | task_type | date_start          | date_due            | taskable_type | taskable_id |
+----+------+--------+-------------+----------+---------------------+---------------------+-----------+---------------------+---------------------+---------------+-------------+
|  1 | NULL |      2 | NULL        |        1 | 2014-09-24 17:53:58 | 2014-09-24 17:53:58 |         1 | 2014-09-25 11:30:00 | 2014-09-25 12:00:00 | Lead          |           1 |
|  2 | NULL |      2 | NULL        |        1 | 2014-09-25 12:45:50 | 2014-09-25 12:45:50 |         1 | 2014-09-25 12:45:00 | 2014-09-25 13:15:00 | Lead          |           2 |

mysql> SELECT id, appointment_status, created_at, updated_at FROM leads;
+----+--------------------+---------------------+---------------------+
| id | appointment_status | created_at          | updated_at          |
+----+--------------------+---------------------+---------------------+
|  1 |                  1 | 2014-09-24 17:45:12 | 2014-09-24 17:53:58 |
|  2 |                  1 | 2014-09-25 12:45:46 | 2014-09-25 12:45:50 |

推荐答案

一种没有用户变量的方法:

An approach without uservars:

SELECT
    tasks.*
FROM
    (SELECT taskable_id,
        MIN(t.created_at) created_at
    FROM
        tasks t
    JOIN
        leads l USING (taskable_id)
    WHERE
        taskable_id IN (1,2)
        AND t.taskable_type = 'Lead'
        AND l.appointment_status = 1
    GROUP BY taskable_id
    ) x
JOIN
    tasks
    USING (taskable_id, created_at)

这篇关于在 Mysql 中对子查询使用 LIMIT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！