oracle-sql计算
1.计算当前时间最近的15分整点数据
select rdc.city_code as city_id, rdc.city_name, rdc.DIS_FAIL_ORDER_NUM as RES_PREE_FAILURE_NUM, --以下是计算最近的整分时间 to_char(sysdate, 'hh24') || ':' || CASE trunc(to_char(sysdate, 'mi') / 15, 0) WHEN 0 THEN '00' WHEN 1 THEN '15' WHEN 2 THEN '30' WHEN 3 THEN '45' ELSE '00' END as HOUR_MIN from res_dis_city rdc where rdc.create_date = (select max(t.create_date) from res_dis_city t);
2.根据地市递归计算地市底下的所有区县
and ba.area_id in (SELECT b.area_id FROM bfm_area b START WITH b.area_id = :areaId CONNECT BY PRIOR area_id = parent_id)
3.根据某个字段相同,其他字段排序 获取第N行数据
select * from (select (case when ua.grade in ('C3', 'C4') then (select area_name from uos_area where area_id = ua.parent_id) else ua.area_name end) as pAreaName, ua.area_name as areaName, (select gridname from res_dd_grid where gridid = uo.grid_id) as gridName, us.staff_name as staffName, us.mobile_tel as mobileTel, (select unit_name from res_dd_grid where gridid = uo.grid_id) as unitName, decode(pst.traning_status, 0, '未完成', '完成') as traningName, decode(pst.exam_pass, 0, '未考试', 1, '通过', '未通过') as examPassName, us.staff_id, uo.org_tmp_id, uj.job_id, row_number() over(partition by us.staff_id order by uo.org_tmp_id desc, uj.job_id asc) as row_no from t_people_traning_score pst, uos_staff us, uos_job_staff ujs, uos_job uj, uos_job_priv ujp, uos_role ur, uos_org uo, uos_area ua where pst.traning_id = 513 --这个修改成实际值 and us.staff_id = pst.staff_id and ujs.staff_id = us.staff_id and uj.job_id = ujs.job_id and ujp.job_id = uj.job_id and ur.role_id = ujp.role_id and ur.role_name in ('网格长', '社区经理') --这个修改成灵活配置的那种SQL and uo.org_id = uj.org_id and ua.area_id = uo.area_id) where row_no = 1;
4. 获取某个月的最后一天
select * from rpt_chn_index5 where day_id=to_number(to_char(last_day(to_date('201704','yyyymm')),'yyyymmdd'));
5.获取当前第一条数据
where rownum = 1
如果前面的数据先按照时间字段排序了。再用where rownum = 1 可以得出当前最新的数据
6.统计地市+底下所有区县的总数
select count(*) as taoToal, (case ba.grade when 'C3' then ba.area_id when 'C4' then ba.parent_id end) as groupAreaId from TINF_ALARM_ORDER tao LEFT JOIN bfm_area ba on tao.area_id = ba.area_id where 1 = 1 and tao.Fault_Type = 'MFS' group by (case ba.grade when 'C3' then ba.area_id when 'C4' then ba.parent_id end)
7.高效删除重复记录的方法
DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO )