sqlite 的 limit

原贴:点击打开链接

drop table if exists student;
create table student( id INT PRIMARY KEY,[name] NVARCHAR(20), classNo INT,gender char(1) );
insert into student(id,[name],classNo,gender) values(1,'小明a',1,'男');
insert into student(id,[name],classNo,gender) values(2,'小明b',1,'男');
insert into student(id,[name],classNo,gender) values(3,'小明c',1,'男');
insert into student(id,[name],classNo,gender) values(4,'小明d',1,'男');
insert into student(id,[name],classNo,gender) values(5,'小明e',1,'男');
insert into student(id,[name],classNo,gender) values(6,'小明f',1,'男');
insert into student(id,[name],classNo,gender) values(7,'小明g',1,'男');
insert into student(id,[name],classNo,gender) values(8,'小明h',1,'男');
insert into student(id,[name],classNo,gender) values(9,'小明i',1,'男');
insert into student(id,[name],classNo,gender) values(10,'小明j',1,'男');
insert into student(id,[name],classNo,gender) values(11,'小明k',1,'男');
insert into student(id,[name],classNo,gender) values(12,'小明k',2,'男');

insert into student(id,[name],classNo,gender) values(13,'小红1',1,'女');
insert into student(id,[name],classNo,gender) values(14,'小红2',1,'女');
insert into student(id,[name],classNo,gender) values(15,'小红3',1,'女');
insert into student(id,[name],classNo,gender) values(16,'小红4',1,'女');
insert into student(id,[name],classNo,gender) values(17,'小红5',1,'女');
insert into student(id,[name],classNo,gender) values(18,'小红6',1,'女');
insert into student(id,[name],classNo,gender) values(19,'小红7',1,'女');
insert into student(id,[name],classNo,gender) values(20,'小红8',1,'女');
insert into student(id,[name],classNo,gender) values(21,'小红9',1,'女');
insert into student(id,[name],classNo,gender) values(22,'小红10',1,'女');
insert into student(id,[name],classNo,gender) values(23,'小红11',1,'女');
insert into student(id,[name],classNo,gender) values(24,'小红12',2,'女');

select * from (
	select * from student where classNo=1 and gender='男' order by id limit 0,10
) as t1
union all
select * from (
	select * from student where classNo=1 and gender='女' order by id limit 0,10
) as t2 
order by gender desc,id
/*
id	name	classNo	gender
1	小明a	1	        男
2	小明b	1	        男
3	小明c	1	        男
4	小明d	1	        男
5	小明e	1	        男
6	小明f	1	        男
7	小明g	1	        男
8	小明h	1	        男
9	小明i	1	        男
10	小明j	1	        男
13	小红1	1	        女
14	小红2	1	        女
15	小红3	1	        女
16	小红4	1	        女
17	小红5	1	        女
18	小红6	1	        女
19	小红7	1	        女
20	小红8	1	        女
21	小红9	1	        女
22	小红10	1	        女
*/
相关文章
相关标签/搜索