例如一张t_user表
id name age
1 xx 20 *
2 xx 20 *
3 xx 19
4 xx 13
5 xx 30 *
6 xx 30 *
7 xx 19
8 xx 30
9 xx 18
我想通过一条查询语句将相邻(id升序,当然也可能存在间断)的age相等的用户查询出来,就是那些星号标出的记录。如何查询呢??
id name age
1 xx 20 *
2 xx 20 *
3 xx 19
4 xx 13
5 xx 30 *
6 xx 30 *
7 xx 19
8 xx 30
9 xx 18
我想通过一条查询语句将相邻(id升序,当然也可能存在间断)的age相等的用户查询出来,就是那些星号标出的记录。如何查询呢??
with t1 as
(select 1 id,'xx' name,20 age from dual
union all
select 2 id,'xx' name,20 age from dual
union all
select 3 id,'xx' name,19 age from dual
union all
select 4 id,'xx' name,13 age from dual
union all
select 5 id,'xx' name,30 age from dual
union all
select 6 id,'xx' name,30 age from dual
union all
select 7 id,'xx' name,19 age from dual
union all
select 8 id,'xx' name,30 age from dual
union all
select 9 id,'xx' name,18 age from dual
)
select id,name,age,
case when age = next_age or age=prior_age then '*' else null end
from
(
select id,name,age,
lag(age) over(order by id) prior_age,
lead(age) over(order by id) next_age
from t1
) t
over函数中的order by指定前后的标准
drop table student;
create table student(id int,name varchar2(20),age int);
insert into student values (1,'aa',20);
insert into student values (2,'bb',20);
insert into student values (3,'cc',19);
insert into student values (4,'dd',13);
insert into student values (5,'ee',30);
insert into student values (6,'ff',30);
insert into student values (7,'gg',19);
insert into student values (8,'hh',30);
insert into student values (9,'ii',18);
commit;select *
from student a,student b
where (a.id=b.id-1 or a.id=b.id+1)
and a.age=b.age
drop table student;
create table student(id int,name varchar2(20),age int);
insert into student values (1,'aa',20);
insert into student values (2,'bb',20);
insert into student values (3,'cc',19);
insert into student values (4,'dd',13);
insert into student values (5,'ee',30);
insert into student values (6,'ff',30);
insert into student values (7,'gg',19);
insert into student values (8,'hh',30);
insert into student values (9,'ii',18);
commit;select *
from student a,student b
where (a.id=b.id-1 or a.id=b.id+1)
and a.age=b.age
select a.*
from student a,student b
where (a.id=b.id-1 or a.id=b.id+1)
and a.age=b.age
order by 1 asc ,2 asc