有表 Table1 字段数据如下:
FILE1 FILE2 FILE3
y 6 f
y 5 e
x 4 d
z 3 c
x 2 b
x 1 a
我想用SQL语句组织上面的数据得到如下结果:
y 6 f 2
x 4 d 1
z 3 c 1
x 2 b 2
也就是按照FILE2字段的顺序,如果发现下面的一条数据在 FILE1 字段上跟它一样就合并成为一条数据
FILE1 FILE2 FILE3
y 6 f
y 5 e
x 4 d
z 3 c
x 2 b
x 1 a
我想用SQL语句组织上面的数据得到如下结果:
y 6 f 2
x 4 d 1
z 3 c 1
x 2 b 2
也就是按照FILE2字段的顺序,如果发现下面的一条数据在 FILE1 字段上跟它一样就合并成为一条数据
(
t01 varchar2(1)
,t02 int
,t03 varchar2(1)
)insert into tt values('y',6,'f');
insert into tt values('y',5,'e');
insert into tt values('x',4,'d');
insert into tt values('z',3,'c');
insert into tt values('x',2,'b');
insert into tt values('x',1,'a');
commit;
--SQL:select t01,t02,t03,bb
from
(
select t01,t02,t03,mm,case when mm=t01 then 2 else 1 end bb,
lag(t01) over(partition by 1 order by 1) aa
from
(
select t01,t02,t03,lead(t01) over(partition by 1 order by 1) mm
from tt
order by t02 desc
)
)
where nvl(aa,1)<>T01--RESULT:y 6 f 2
x 4 d 1
z 3 c 1
x 2 b 2
select file1,file2,file3
from Table1
where file2 in (
select b.file2
from Table1 a,Table1 b
where a.file1<>b.file1 and a.file2=b.file2+1)
from Table1 x,
(select file2,(count(*) over(partition by file1 order by file2)) as t_count
from Table1) y
where x.file2 in (
select b.file2
from Table1 a,Table1 b
where a.file1 <>b.file1 and a.file2=b.file2+1)
and x.file2=y.file2这partition by还不大会用,不知道对不,呵呵。
SQL> SELECT * FROM TABLE1;FILE1 FILE2 FILE3
----- ---------- -----
Y 6 F
Y 5 E
X 4 D
Z 3 C
X 2 B
X 1 A
X 0 Z7 rows selectedSQL> SELECT FILE1,
2 FILE2,
3 FILE3,
4 COUNTS
5 FROM (
6 SELECT T2.*,
7 COUNT(1) OVER(PARTITION BY FILE1,FR) "COUNTS",
8 ROW_NUMBER() OVER(PARTITION BY FILE1,FR ORDER BY FILE2 DESC) "RN"
9 FROM (
10 SELECT T1.*,
11 FILE2 - ROW_NUMBER() OVER(PARTITION BY FILE1 ORDER BY FILE2) "FR"
12 FROM TABLE1 T1
13 )T2
14 )T3
15 WHERE RN = 1
16 ORDER BY FILE2 DESC;FILE1 FILE2 FILE3 COUNTS
----- ---------- ----- ----------
Y 6 F 2
X 4 D 1
Z 3 C 1
X 2 B 3SQL>
from (select t01,
t02,
t03,
nvl(lag(t01) over(order by t02 desc),'%') mm
from tt)
where t01 <> mm
From table1 t
left Join (Select file2, count(file1) nub From table1 Group by File2) t1
on t.file2=t1.file2
where t1.file2 is not null