ta表:
create table ta(id int,name varchar(10));
insert into ta values(1,'a');
insert into ta values(1,'a');
insert into ta values(2,'b');
insert into ta values(3,'c');id和name两个字段
id | name
1 | a
2 | b
1 | a
3 | c select id from ta where 1=1 group by id结果是3条:
id
1
2
3分页sql:
第1页:
SELECT id FROM (SELECT A.id,ROWNUM RN FROM (SELECT id FROM ta) A WHERE ROWNUM <=1) WHERE RN >=1 group by id;结果是:
id
1
第2页:
SELECT id FROM (SELECT A.id,ROWNUM RN FROM (SELECT id FROM ta) A WHERE ROWNUM <=2) WHERE RN >=2 group by id;结果是:
id
1第3页:
SELECT id FROM (SELECT A.id,ROWNUM RN FROM (SELECT id FROM ta) A WHERE ROWNUM <=3) WHERE RN >=3 group by id;结果是:
id
2第4页:
SELECT id FROM (SELECT A.id,ROWNUM RN FROM (SELECT id FROM ta) A WHERE ROWNUM <=4) WHERE RN >=4 group by id;结果是:
id
3我分页一共3条记录,有两次取出结果id:1,要翻4页.
这不是变成4条记录了吗.怎样才能把重复的id:1去掉.
create table ta(id int,name varchar(10));
insert into ta values(1,'a');
insert into ta values(1,'a');
insert into ta values(2,'b');
insert into ta values(3,'c');id和name两个字段
id | name
1 | a
2 | b
1 | a
3 | c select id from ta where 1=1 group by id结果是3条:
id
1
2
3分页sql:
第1页:
SELECT id FROM (SELECT A.id,ROWNUM RN FROM (SELECT id FROM ta) A WHERE ROWNUM <=1) WHERE RN >=1 group by id;结果是:
id
1
第2页:
SELECT id FROM (SELECT A.id,ROWNUM RN FROM (SELECT id FROM ta) A WHERE ROWNUM <=2) WHERE RN >=2 group by id;结果是:
id
1第3页:
SELECT id FROM (SELECT A.id,ROWNUM RN FROM (SELECT id FROM ta) A WHERE ROWNUM <=3) WHERE RN >=3 group by id;结果是:
id
2第4页:
SELECT id FROM (SELECT A.id,ROWNUM RN FROM (SELECT id FROM ta) A WHERE ROWNUM <=4) WHERE RN >=4 group by id;结果是:
id
3我分页一共3条记录,有两次取出结果id:1,要翻4页.
这不是变成4条记录了吗.怎样才能把重复的id:1去掉.
SELECT distinct id FROM ta ;去掉id重复得记录。
第1页:
SELECT id FROM (SELECT A.id,ROWNUM RN FROM (SELECT id FROM ta group by id) A WHERE ROWNUM <=1) WHERE RN > =1 ;
这是10 - 20行