Mysql alternative for LIMIT inside subquery in mys

2019-06-27 15:51发布

问题:

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)

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).

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

Thanks to Mark Byers

回答1:

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.

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

Result:

Test data:

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);