mysql 5.1 limit_mysql 5.1.49中子查询中LIMIT的MySQL替代方法

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

解决方案

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:

1, 1990

2, 1990

5, 1990

4, 1991

7, 1991

8, 1991

6, 1992

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