现有表T F1 F2 F3 ....
a xx xx //1
a xx xx //2
b xx xx //3
b xx xx //4
b xx xx //5
c xx xx //6 表是自动排序的 其余未知
取的是F1字段值相邻的纪录 现在要得到的结果是 a xx xx //2
b xx xx //5
c xx xx //6 请大虾指教!
a xx xx //1
a xx xx //2
b xx xx //3
b xx xx //4
b xx xx //5
c xx xx //6 表是自动排序的 其余未知
取的是F1字段值相邻的纪录 现在要得到的结果是 a xx xx //2
b xx xx //5
c xx xx //6 请大虾指教!
FROM TDISTINCT关键字是删除重复字段的,应该可以的吧.
select 'a','xx','xx' from dual
union all
select 'a','xx','xx' from dual
union all
select 'b','xx','xx' from dual
union all
select 'b','xx','xx' from dual
union all
select 'b','xx','xx' from dual
union all
select 'c','xx','xx' from dual;declare
cursor cur is select f1,f2,f3,rownum from test1 order by rownum desc;
rtype cur%rowtype;
temp varchar2(10);
begin
temp:=' ';
open cur ;
loop
fetch cur into rtype;
EXIT WHEN cur%NOTFOUND;
if temp <> rtype.f1 then
dbms_output.put_line(rtype.f1||' '||rtype.f2||' '||rtype.f3||' '||rtype.rownum);
temp:=rtype.f1;
end if;
end loop;
close cur;
end;
--Result
c xx xx 6
b xx xx 5
a xx xx 2--有了这个结果你就可以形成类似下面的sql
select f1,f2,f3 from (
select 'c' f1,'xx' f2,'xx' f3,6 rn from dual
union all
select 'b' f1,'xx' f2,'xx' f3,5 rn from dual
union all
select 'a' f1,'xx' f2,'xx' f3,2 rn from dual
) t
order by t.rn
--Result
a xx xx //2
b xx xx //5
c xx xx //6
2 (
3 select 'a' as f1, 'xx' as f2, 'xx' as f3, '//1' as f4 from dual union all
4 select 'a' as f1, 'xx' as f2, 'xx' as f3, '//2' as f4 from dual union all
5 select 'b' as f1, 'xx' as f2, 'xx' as f3, '//3' as f4 from dual union all
6 select 'b' as f1, 'xx' as f2, 'xx' as f3, '//4' as f4 from dual union all
7 select 'b' as f1, 'xx' as f2, 'xx' as f3, '//5' as f4 from dual union all
8 select 'c' as f1, 'xx' as f2, 'xx' as f3, '//6' as f4 from dual
9 );F1 F2 F3 MAX(F4
-- ---- ---- ------
a xx xx //2
b xx xx //5
c xx xx //6
SQL> select distinct f1, f2, max(f3)over(partition by f1, f2) from
2 (
3 select 'a' as f1, 'xx' as f2, 'xx' as f3 from dual union all
4 select 'a' as f1, 'xx' as f2, 'xx' as f3 from dual union all
5 select 'b' as f1, 'xx' as f2, 'xx' as f3 from dual union all
6 select 'b' as f1, 'xx' as f2, 'xx' as f3 from dual union all
7 select 'b' as f1, 'xx' as f2, 'xx' as f3 from dual union all
8 select 'c' as f1, 'xx' as f2, 'xx' as f3 from dual
9 );F1 F2 MAX(
-- ---- ----
a xx xx
b xx xx
c xx xx
b xx xx //5
c xx xx //6