在某个表里hphm和clbh是一对多的关系,hphm在表里面是有重复的,就是说对于某个hphm的值,也可能有多个 clbh 字段值与其对应啊. 要求选出五天中每天每条hphm的相关信息,hphm不能相同,下面的语句只是选出一天的
select hphm,clbh from (select hphm,clbh,row_number() over(partition by hphm order by clbh) rn from xxfl ) where rn=1 and jllx='0' and scbj=0 and (jgsj between to_date( '2008-08-30 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2008-08-31 00:00:00','yyyy-mm-dd hh24:mi:ss'));
如果选出五天的怎么做呢,可能要用到存储过程
请教各位不吝赐教
select hphm,clbh from (select hphm,clbh,row_number() over(partition by hphm order by clbh) rn from xxfl ) where rn=1 and jllx='0' and scbj=0 and (jgsj between to_date( '2008-08-30 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2008-08-31 00:00:00','yyyy-mm-dd hh24:mi:ss'));
如果选出五天的怎么做呢,可能要用到存储过程
请教各位不吝赐教
select hphm,clbh from (select hphm,clbh,row_number() over(partition by hphm order by clbh) rn from xxfl ) where rn=1 and jllx='0' and scbj=0 and (jgsj between to_date( '2008-08-30 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2008-08-31 00:00:00','yyyy-mm-dd hh24:mi:ss'));
select hphm,clbh from (select hphm,clbh,row_number() over(partition by hphm order by clbh) rn from xxfl ) where rn=1 and jllx='0' and scbj=0 and (jgsj between to_date( '2008-08-31 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2008-08-32 00:00:00','yyyy-mm-dd hh24:mi:ss'));
select hphm,clbh from (select hphm,clbh,row_number() over(partition by hphm order by clbh) rn from xxfl ) where rn=1 and jllx='0' and scbj=0 and (jgsj between to_date( '2008-08-32 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2008-08-33 00:00:00','yyyy-mm-dd hh24:mi:ss'));
select hphm,clbh from (select hphm,clbh,row_number() over(partition by hphm order by clbh) rn from xxfl ) where rn=1 and jllx='0' and scbj=0 and (jgsj between to_date( '2008-08-33 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2008-08-34 00:00:00','yyyy-mm-dd hh24:mi:ss'));
select hphm,clbh from (select hphm,clbh,row_number() over(partition by hphm order by clbh) rn from xxfl ) where rn=1 and jllx='0' and scbj=0 and (jgsj between to_date( '2008-08-34 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2008-08-35 00:00:00','yyyy-mm-dd hh24:mi:ss'));
有没有简单的办法??
select hphm,clbh from (select hphm,clbh,row_number() over(partition by hphm order by clbh) rn from xxfl ) where rn=1 and jllx='0' and scbj=0 and (jgsj between to_date( '2008-08-30 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2008-08-31 00:00:00','yyyy-mm-dd hh24:mi:ss'));
select hphm,clbh from (select hphm,clbh,row_number() over(partition by hphm order by clbh) rn from xxfl ) where rn=1 and jllx='0' and scbj=0 and (jgsj between to_date( '2008-08-31 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2008-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss'));
select hphm,clbh from (select hphm,clbh,row_number() over(partition by hphm order by clbh) rn from xxfl ) where rn=1 and jllx='0' and scbj=0 and (jgsj between to_date( '2008-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2008-09-02 00:00:00','yyyy-mm-dd hh24:mi:ss'));
select hphm,clbh from (select hphm,clbh,row_number() over(partition by hphm order by clbh) rn from xxfl ) where rn=1 and jllx='0' and scbj=0 and (jgsj between to_date( '2008-09-02 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2008-09-03 00:00:00','yyyy-mm-dd hh24:mi:ss'));
select hphm,clbh from (select hphm,clbh,row_number() over(partition by hphm order by clbh) rn from xxfl ) where rn=1 and jllx='0' and scbj=0 and (jgsj between to_date( '2008-09-03 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2008-09-04 00:00:00','yyyy-mm-dd hh24:mi:ss'));
from (select hphm,
clbh,
to_char(JGSJ, 'yyyy-mm-dd') t_day,
row_number() over(partition by hphm, to_char(JGSJ, 'yyyy-mm-dd') order by to_char(JGSJ, 'yyyy-mm-dd')) rn
from test
where JGSJ between sysdate and (sysdate + 5)
group by hphm, clbh, to_char(JGSJ, 'yyyy-mm-dd')
order by to_char(JGSJ, 'yyyy-mm-dd'))
where rn = 1
order by hphm,t_day;
select hphm, clbh, t_day
from (select hphm,
clbh,
to_char(JGSJ, 'yyyy-mm-dd') t_day,
row_number() over(partition by hphm, to_char(JGSJ, 'yyyy-mm-dd') order by to_char(JGSJ, 'yyyy-mm-dd')) rn
from xxfl
where JGSJ between sysdate and (sysdate + 5))
where rn = 1
order by hphm,t_day;