mysql 5.1.49中子查询中LIMIT的mysql替代方案 [英] Mysql alternative for LIMIT inside subquery in mysql 5.1.49

问题描述

SELECT student_id FROM `students` AS s1
WHERE student_id IN 
  (SELECT s2.student_id FROM `students` AS s2
     WHERE s1.year_of_birth = s2.year_of_birth
     LIMIT 10)

无法在我的服务器上处理此查询.它丢弃错误，表示该版本的mysql不支持子查询等内部的限制(错误1235).

Can't process this query on my server. It drops errors, that says that this version of mysql doesn't support limit inside subqueries etc(ERROR 1235).

我的mysql 5.1.49版本是否有解决方案?

Is there any solution for my version of mysql 5.1.49?

SELECT
    id,
    region
FROM (
    SELECT
        region,
        id,
        @rn := CASE WHEN @prev_region = region
                    THEN @rn + 1
                    ELSE 1
               END AS rn,
        @prev_region := region
    FROM (SELECT @prev_region := NULL) vars, ads T1
    ORDER BY region, id DESC
) T2
WHERE rn <= 4
ORDER BY region, id

感谢马克·拜尔斯

推荐答案

我想您希望每个生日都有十个学生.这是每组最多的查询，您可以搜索堆栈溢出，以了解如何在MySQL中完成此操作.

I think you want any ten students with each birthdate. This is a greatest-n-per-group query and you can search Stack Overflow to see how this can be done in MySQL.

如果MySQL支持ROW_NUMBER函数将很容易，但是由于它不能，您可以使用变量来模拟它.例如，每个出生日期要让3名学生参加，您可以这样做:

It would be easy if MySQL supported the ROW_NUMBER function, but since it does not you can emulate it using variables. For example to get 3 students for each birth date you could do it like this:

SELECT
    student_id,
    year_of_birth
FROM (
    SELECT
        year_of_birth,
        student_id,
        @rn := CASE WHEN @prev_year_of_birth = year_of_birth
                    THEN @rn + 1
                    ELSE 1
               END AS rn,
        @prev_year_of_birth := year_of_birth
    FROM (SELECT @prev_year_of_birth := NULL) vars, students T1
    ORDER BY year_of_birth, student_id DESC
) T2
WHERE rn <= 3
ORDER BY year_of_birth, student_id

结果:

1, 1990
2, 1990
5, 1990
4, 1991
7, 1991
8, 1991
6, 1992

测试数据:

CREATE TABLE students (student_id INT NOT NULL, year_of_birth INT NOT NULL);
INSERT INTO students (student_id, year_of_birth) VALUES
(1, 1990),
(2, 1990),
(3, 1991),
(4, 1991),
(5, 1990),
(6, 1992),
(7, 1991),
(8, 1991);

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