帮忙指导个sql 谢谢

显示全部楼层 · 2010-3-1 11:19:49

SALENO
OLD_MD
NEW_MD
SEPARATE_TIME
RN
100018
110342
110343
2010-11-10 19:44
1
100018
110343
100037
2010-11-12 19:44
2
100076
110342
110343
2010-11-9 19:44
1
100076
110343
100038
2010-11-13 19:44
2

现在我想要的数据是
saleno
md
start
end
100018110342 1900-01-01 2010-11-10
100018110343 2010-11-10 2010-11-12
100018100037 2010-11-12 now
。
。
。
谢谢各位大大了

千问 · 2010-3-1 11:19:49

SALENO
OLD_MD
NEW_MD
SEPARATE_TIME
RN
100018
110342
110343
2010-11-10
1
100018
110343
100037
2010-11-12
2
select saleno,old_md md,rq start,lead(rq) over(order by rq)end
from(
select saleno,0,old_md,to_date('1900.01.01','yyyy.mm.dd') rq,0 from cs where rn=1
union all
select * from cs )

千问 · 2010-3-1 11:19:49

不行啊老大

千问 · 2010-3-1 11:19:49

各位大哥帮帮忙啊救急啊

千问 · 2010-3-1 11:19:49

我写的有点繁，并且把你的'now'改成sysdate了，不过貌似还可以用，如果你的rn只有1，2两级的话。

with ta as (select saleno,old_md,separate_time,rn from tts union
select saleno,new_md,separate_time,rn+1 rn from tts)
select saleno,old_md md,min(s)startt,max(e)endd from
(select saleno,
old_md,
decode(rn,1,to_date('19000101','yyyymmdd'),2,lag(separate_time)over(partition by saleno order by rn),3,separate_time) s,
decode(rn,1,separate_time,2,lead(separate_time)over(partition by saleno order by rn),3,sysdate) e,rn from ta) group by saleno,old_md

千问 · 2010-3-1 11:19:49

with v as (
SELECT saleno, old_md, new_md, separate_time, rn
FROM t
UNION ALL
(SELECT saleno, max(new_md)keep(dense_rank last order by rn), NULL, NULL, MAX(rn) + 1
FROM t GROUP BY saleno))
SELECT saleno
,old_md md
,lag(separate_time, 1, to_date('1900-01-01', 'yyyy-mm-dd')) over(PARTITION BY saleno ORDER BY rn) start_time
,nvl(separate_time,sysdate) end_time --假设separate_time 非空
,rn
FROM v

千问 · 2010-3-1 11:19:49

呵呵谢谢 now 就是sysdate嘛但是不是只有1 2 两级有可能会更多

千问 · 2010-3-1 11:19:49

现在在家明天测试谢谢各位了

千问 · 2010-3-1 11:19:49

SELECT * FROM
(
SELECT saleno, old_md md, lag(separate_time, 1, to_date('1900-01-01', 'yyyy-mm-dd'))OVER(PARTITION BY saleno ORDER BY rn) start_time, separate_time end_time, rn
FROM dzhuo
UNION
SELECT saleno, new_md md, separate_time end_time, lead(separate_time, 1, to_date(sysdate))OVER(PARTITION BY saleno ORDER BY rn), rn+1 rn
FROM dzhuo
)
order by saleno, rn
;

千问 · 2010-3-1 11:19:49

谢谢各位老大搞定啦