给定时间可能是7天(例如下面的从20081203150956-20081210150956),选出每天的hphm,要求一天之内hphm的值不能重复,请问怎样修改?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 to_date('20081203150956','yyyy-mm-dd hh24:mi:ss') and to_date('20081210150956','yyyy-mm-dd hh24:mi:ss') )
where rn = 1
order by hphm,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 to_date('20081203150956','yyyy-mm-dd hh24:mi:ss') and to_date('20081210150956','yyyy-mm-dd hh24:mi:ss') )
where rn = 1
order by hphm,t_day;
你的语句不符合你的需求吗,感觉没问题啊.
clbh是可以随便取吗,如果无所谓,简化如下面:select hphm, min(clbh),to_char(JGSJ, 'yyyy-mm-dd') t_day
from xxfl
group by hphm,to_char(JGSJ, 'yyyy-mm-dd')
order by 1, 3
;