一直困扰的ORACLE SQL 问题,求助

njsteve 2013-05-02 11:17:18
START_CI name_id TIME_STAMP
11933 test01 2013-1-7 11:01
11933 test01 2013-1-7 13:18
11933 test01 2013-1-7 18:18
38165 test02 2013-1-7 8:36
38165 test02 2013-1-7 9:36
3244 test02 2013-1-7 13:20
38165 test02 2013-1-7 18:36
3210 test02 2013-1-7 23:39
测试表名为:TEST 三列如上:START_CI name_id TIME_STAMP
想要的结果为:
11933 test01 2013-1-7 11:01
38165 test02 2013-1-7 8:36
3244 test02 2013-1-7 13:20
38165 test02 2013-1-7 18:36
3210 test02 2013-1-7 23:39
解释:
根据不同的name_id分组。合并START_CI相同的记录,取TIME_STAMP最小值,
但合并注意。不联系的不可合并,比如:
38165 test02 2013-1-7 8:36
38165 test02 2013-1-7 9:36
3244 test02 2013-1-7 13:20
38165 test02 2013-1-7 18:36
这四条记录,中间有条START_CI为3244 让teset02的用户 START_CI为38165变的不连续的行,只能合并前两行,对于test01用户前三行START_CI都是11933且都是上下行都是联系的11933,没有被其他不同的分隔开,可以合并一条
...全文
88 点赞 收藏 6
写回复
6 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
njsteve 2013-05-06
如果把 insert into TEST (START_CI,name_id,TIME_STAMP) values(38165,'test02', to_date('201301079:36:00','yyyymmdd hh24:mi:ss'));
这个记录改为插进表中(修改的为START_CI):
insert into TEST (START_CI,name_id,TIME_STAMP) values(448165,'test02', to_date('201301079:36:00','yyyymmdd hh24:mi:ss'));
那么
with t as (select t.*, case when t.START_CI <> lead(START_CI) over(order by rownum) and t.START_CI <> lag(START_CI) over(order by rownum) then 1 else 0 end flag from test t) select START_CI, name_id, TIME_STAMP from (select t.*, row_number() over(partition by START_CI, flag order by TIME_STAMP) rn from t) where rn = 1;
语句就不通用了。麻烦楼主指导指导
回复
njsteve 2013-05-02
u010412956 能有个联系方式吗 ?
回复
njsteve 2013-05-02
非常感谢 u010412956
回复
hh7yx 2013-05-02
with t as
 (select t.*,
         case
           when t.START_CI <> lead(START_CI)
            over(order by rownum) and t.START_CI <> lag(START_CI)
            over(order by rownum) then
            1
           else
            0
         end flag
    from test t)
select START_CI, name_id, TIME_STAMP
  from (select t.*,
               row_number() over(partition by START_CI, flag order by TIME_STAMP) rn
          from t)
 where rn = 1;
回复
njsteve 2013-05-02
create table TEST ( START_CI NUMBER(32), name_id VARCHAR2(25), TIME_STAMP DATE ); insert into TEST (START_CI,name_id,TIME_STAMP) values(11933,'test01',to_date('2013010711:01:00','yyyymmdd hh24:mi:ss')); insert into TEST (START_CI,name_id,TIME_STAMP) values(11933,'test01', to_date('2013010713:18:00','yyyymmdd hh24:mi:ss')); insert into TEST (START_CI,name_id,TIME_STAMP) values(11933,'test01', to_date('2013010718:18:00','yyyymmdd hh24:mi:ss')); insert into TEST (START_CI,name_id,TIME_STAMP) values(38165,'test02', to_date('201301079:36:00','yyyymmdd hh24:mi:ss')); insert into TEST (START_CI,name_id,TIME_STAMP) values(38165,'test02', to_date('201301079:36:00','yyyymmdd hh24:mi:ss')); insert into TEST (START_CI,name_id,TIME_STAMP) values(3244,'test02', to_date('2013010713:20:00','yyyymmdd hh24:mi:ss')); insert into TEST (START_CI,name_id,TIME_STAMP) values(38165,'test02', to_date('2013010718:36:00','yyyymmdd hh24:mi:ss')); insert into TEST (START_CI,name_id,TIME_STAMP) values(3210,'test02', to_date('20130107 23:39:00','yyyymmdd hh24:mi:ss'));
回复
hh7yx 2013-05-02
给出create 和 insert 脚本。。
回复
相关推荐
发帖
Oracle
创建于2007-09-28

1.6w+

社区成员

Oracle开发相关技术讨论
申请成为版主
帖子事件
创建了帖子
2013-05-02 11:17
社区公告
暂无公告