复杂sql

最新推荐文章于 2024-07-25 10:28:23 发布

bzsjy

最新推荐文章于 2024-07-25 10:28:23 发布

阅读量135

点赞数

本文链接：https://blog.csdn.net/bzsjy/article/details/107197499

版权

select ‘酒精浓度’ as check_key,lv_start||’-’||lv_end age_level,sum(decode(IS_CHECK,1,1,0)) check_nums, round(avg(ALCOHOL_VAL),2) avg_val from
(select level,case when ((level-2)*3)+21<21 then 0 else ((level-2)*3)+21 end lv_start,((level-1)*3)+21 lv_end from dual connect by level <= 14)a,
(select TRUNC(months_between(sysdate, t.birthday)/12) AS age,IS_CHECK,ALCOHOL_VAL
from TM_ALCOHOL_DRIVER_CHECK_INFO t where CHECK_DATE >trunc(sysdate-1) ) b
where b.age>a.lv_start and b.age<=a.lv_end
group by lv_start||’-’||lv_end
union all
select ‘血氧’ as check_key,lv_start||’-’||lv_end age_level,sum(decode(IS_CHECK,1,1,0)) check_nums,
round(avg(BLOOD_OXYGEN_VAL),2) avg_val from
(select level,case when ((level-2)*3)+21<21 then 0 else ((level-2)*3)+21 end lv_start,((level-1)*3)+21 lv_end from dual connect by level <= 14)a,
(select TRUNC(months_between(sysdate, t.birthday)/12) AS age,IS_CHECK,BLOOD_OXYGEN_VAL
from TM_ALCOHOL_DRIVER_CHECK_INFO t where CHECK_DATE >trunc(sysdate-1) ) b
where b.age>a.lv_start and b.age<=a.lv_end
group by lv_start||’-’||lv_end;