1
2
3

select * from table where xxx limit a,b;

select * from table where id in (select id from table where xxx limit a,b);

1
2
3
4
5
6
7
8
9

CREATE TABLE `salary_static` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `school_id` int(11) NOT NULL COMMENT '学校id',
  `student_id` int(11) NOT NULL COMMENT '毕业生id',
  `salary` int(11) NOT NULL DEFAULT '0' COMMENT '毕业薪水',
  `year` int(11) NOT NULL COMMENT '毕业年份',
  PRIMARY KEY (`id`),
  KEY `year_key` (`year`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='毕业生薪水数据统计';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

delimiter  //
CREATE PROCEDURE init_salary_static() 
BEGIN 
	DECLARE year INT;
	DECLARE schid INT;
	DECLARE stuid INT;
	SET year = 2000;
	WHILE year < 2020 DO
		START TRANSACTION; 
		SET schid = 1;
		WHILE schid < 100 DO
			SET stuid = 1;
			WHILE stuid < 1000 DO
				insert into salary_static(school_id,student_id,salary,year) values (schid,stuid,floor(rand()*10000),year);
				SET stuid = stuid + 1;
			END WHILE;
			SET schid = schid + 1;
		END WHILE;
		SET year = year + 1;
		COMMIT; 
	END WHILE;
END //
delimiter ;

call init_salary_static();

1
2
3
4
5

select * from salary_static where year < 2010 limit 500000,10;

select s.* from salary_static s join 
(select id from salary_static where year < 2010 limit 500000,10)
as tmp on (tmp.id = s.id);

1
2
3
4
5

select * from salary_static where year < 2010 and salary < 8000 limit 500000,10;

select s.* from salary_static s join 
(select id from salary_static where year < 2010 and salary < 8000 limit 500000,10)
as tmp on (tmp.id = s.id);