数据库数据:
ID ROOM starttime endtime
1 1-101 2010-01-20 11:01 2010-01-20 12:01
2 1-101 2010-01-20 12:02 2010-13-20 12:01
3 1-101 2010-01-20 14:01 2010-15-20 12:01
4 1-101 2010-01-20 15:01 2010-01-20 17:01
5 1-102 2010-01-20 11:01 2010-01-20 12:01查询出数据:
1 1-101 2010-01-20 11:01 2010-01-20 12:01
2 2010-01-20 12:02 2010-13-20 12:01
3 2010-01-20 14:01 2010-15-20 12:01
4 2010-01-20 15:01 2010-01-20 17:01
5 1-102 2010-01-20 11:01 2010-01-20 12:01就是查询出数据那样把2 3 4 行重复的ROOM去掉。应该如何写SQL,感谢!
ID ROOM starttime endtime
1 1-101 2010-01-20 11:01 2010-01-20 12:01
2 1-101 2010-01-20 12:02 2010-13-20 12:01
3 1-101 2010-01-20 14:01 2010-15-20 12:01
4 1-101 2010-01-20 15:01 2010-01-20 17:01
5 1-102 2010-01-20 11:01 2010-01-20 12:01查询出数据:
1 1-101 2010-01-20 11:01 2010-01-20 12:01
2 2010-01-20 12:02 2010-13-20 12:01
3 2010-01-20 14:01 2010-15-20 12:01
4 2010-01-20 15:01 2010-01-20 17:01
5 1-102 2010-01-20 11:01 2010-01-20 12:01就是查询出数据那样把2 3 4 行重复的ROOM去掉。应该如何写SQL,感谢!
select id, case when num is null then room else null end as room,starttime,endtime
from
(
select a.*,leg(room)over(partition by room order by id) as num from tab a
)
create table ta(id int,name varchar2(10));
insert into ta select 1,'name1' from dual;
insert into ta select 2,'name1' from dual;
insert into ta select 3,'name3' from dual;
insert into ta select 4,'name3' from dual;
commit;
select id, case when num is null then name else null end as room
from
(
select a.*,lag(id)over(partition by name order by id) as num from ta a
)
15:59:36 2 (select '1' id,'1-101' room,'2010-01-20 11:01' starttime,'2010-01-20 12:01' endtime from dual
15:59:36 3 union all
15:59:36 4 select '2','1-101','2010-01-20 12:02','2010-13-20 12:01' from dual
15:59:36 5 union all
15:59:36 6 select '3','1-101','2010-01-20 14:01','2010-15-20 12:01' from dual
15:59:36 7 union all
15:59:36 8 select '4','1-101','2010-01-20 15:01','2010-01-20 17:01' from dual
15:59:36 9 union all
15:59:36 10 select '5','1-102','2010-01-20 11:01','2010-01-20 12:01' from dual)
15:59:36 11 select a.id,b.room,starttime,endtime from tb a,(select min(id) id,room from tb group by room) b
15:59:36 12 where a.id = b.id(+) order by 1;ID ROOM STARTTIME ENDTIME
--- ----- ---------------- ----------------
1 1-101 2010-01-20 11:01 2010-01-20 12:01
2 2010-01-20 12:02 2010-13-20 12:01
3 2010-01-20 14:01 2010-15-20 12:01
4 2010-01-20 15:01 2010-01-20 17:01
5 1-102 2010-01-20 11:01 2010-01-20 12:01已用时间: 00: 00: 00.06
select id, case when num is null then room else null end as room,starttime,endtime
from
(
select a.*,lag(room)over(partition by room order by id) as num from tab a
)
不好意思是笔误,应该是lag,是一个分析函数类似于row_number等等
delete from table1
where rowid = (select rowid
from table1 tab1, table1 tab2
where tab1.id = tab2.id
and tab1.rowid > tab2.rowid)
楼主没看我给你的SQL吗,不满足吗? 叹什么气。
是lag 不是leg ,它是一个分析函数
SELECT DECODE(RN, 1, mtmeetnum), destineday,staffname,fromhour,FROMMINUTE
FROM (SELECT t.mtmeetnum,
t.destineday,
t.staffname,
fromhour,
FROMMINUTE,
ROW_NUMBER() OVER(PARTITION BY t.mtmeetnum ORDER BY T.MTMEETNUM ,fromhour,FROMMINUTE DESC) RN
FROM use_mtmeetmessage t
WHERE t.isdelete = 'F'
ORDER BY T.MTMEETNUM ,t.fromhour,T.FROMMINUTE DESC);