mysql 5.1 limit,Mysql替代为mysql 5.1.49中子查询内的LIMIT

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不支持子查询内的限制等(ERROR 1235).

我的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

感谢Mark Byers

解决方法:

我想你想要每个生日的十个学生.这是一个最大的每组查询,您可以在search Stack Overflow中查看如何在MySQL中完成此操作.

如果MySQL支持ROW_NUMBER函数会很容易,但由于它不支持,你可以使用变量来模拟它.例如,为了让每个出生日期有3名学生,您可以这样做：

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,greatest-n-per-group

来源： https://codeday.me/bug/20190626/1299054.html