常用SQL

/*某时间段电池电压、信号强度*/
select ds.areaname,ds.meter_serial_num,
       case when length(ds.date_time_min) = 14 then
       to_date(ds.date_time_min, 'yyyy-mm-dd hh24:mi:ss') else to_date('0000-00-00 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
       end as meterTime,
       ds.date_time,ds.dianya,ds.xinhao 
from (select c.areaname,a.meter_serial_num,t.date_time,t.tariff,
                              max(decode(t.var_code_id,'308',t.raw_value,null)) date_time_min,
                              max(decode(t.var_code_id,'710',t.raw_value,null)) dianya,
                              max(decode(t.var_code_id,'309',t.raw_value,null)) xinhao
       from (select * from met_value_base where var_code_id in (308,710,309) and data_type in (88,89)) t
              inner join sys_meter a on t.meter_id=a.id
              inner join sys_terminal b on a.terminal_Id=b.id
              inner join sys_area c on b.parent_id=c.id
              where c.parentid in (select d.decendent_dept_id from sys_dept_decendent d where d.dept_id=7827822)
       group by c.areaname,a.meter_serial_num, t.date_time,t.tariff
)ds
where ds.date_time_min>'20211225000000' and ds.dianya is not null             
order by  meter_serial_num,date_time,meterTime ;
 

 /*查最新止码143 反向用量140*/  
select c.customer_name,c.phone1,c.customer_address,m.meter_serial_num,
       case when length(ds1.date_time_min) = 14 then
       to_char(to_date(ds1.date_time_min,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
       else '0000-00-00 00:00:00' end as meterTime,
       ds1.consu as useNum
 from sys_meter m
 inner join (select * from(
                    select ROW_NUMBER() over(partition by aa.meter_id order by aa.date_time_min desc) as rn,aa.*
                    from (select t.meter_id, t.date_time,t.tariff,
                                 max(decode(t.var_code_id,'308',t.raw_value,null)) date_time_min,
                                 max(decode(t.var_code_id,'143',t.raw_value,null)) consu
                            from met_value t
                            where t.var_code_id in (308, 143)and t.DATA_TYPE = 89
                             /*and t.date_time >trunc(add_months(sysdate, -4), 'mm')*/
                           group by t.meter_id, t.date_time,t.tariff) aa
                   where aa.date_time_min is not null and aa.consu is not null
                     /*and aa.date_time_min <to_char(sysdate, 'yyyymmddhh24miss')*/) bb
           where bb.rn = 1
  ) ds1 on m.id = ds1.meter_id
  inner join sys_cust c on c.id=m.customer_id
  inner join sys_terminal ter on ter.id=m.terminal_id
  inner join sys_area a on a.id=ter.parent_id
 where a.parentid in (select d.decendent_dept_id from sys_dept_decendent d where d.dept_id=7827822) 
 order by ter.parent_id;

/*单个表基本客户信息、最新上报用量*/
select a.areaname,c.customer_name,c.id_number,c.phone1,c.customer_address,ds1.meter_serial_num,
       case when length(ds1.date_time_min) = 14 then
       to_char(to_date(ds1.date_time_min,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
       else '0000-00-00 00:00:00' end as meterTime,
       ds1.consu as useNum
 from (select * from(
                    select ROW_NUMBER() over(partition by aa.meter_id order by aa.date_time_min desc) as rn,aa.*
                    from (select t.meter_id, t.date_time,t.tariff,sm.meter_serial_num,sm.customer_id,sm.terminal_id,
                                 max(decode(t.var_code_id,'308',t.raw_value,null)) date_time_min,
                                 max(decode(t.var_code_id,'143',t.raw_value,null)) consu
                            from met_value t inner join sys_meter sm on sm.id=t.meter_id
                            where t.var_code_id in (308, 143)and t.DATA_TYPE = 89 and sm.meter_serial_num='331819102097'
                           group by t.meter_id, t.date_time,t.tariff,sm.meter_serial_num,sm.customer_id,sm.terminal_id) aa
                   where aa.date_time_min is not null and aa.consu is not null) bb
           where bb.rn = 1
  ) ds1 
  inner join sys_cust c on c.id=ds1.customer_id
  inner join sys_terminal ter on ter.id=ds1.terminal_id
  inner join sys_area a on a.id=ter.parent_id;
  
 
/*单个表最新上报电池电压、信号强度、阀门状态*/
select ds.meter_id,case when length(ds.date_time_min) = 14 then
       to_date(ds.date_time_min, 'yyyy-mm-dd hh24:mi:ss') else to_date('0000-00-00 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
       end as meterTime,
       ds.dianya,ds.xinhao,ds.valveStatus
from (select ROW_NUMBER() over(partition by ab.meter_id order by ab.date_time_min desc) as rn,ab.*
         from (select t.date_time,t.tariff,t.id as meter_id,
                 max(decode(t.var_code_id,'308',t.raw_value,null)) date_time_min,
                 max(decode(t.var_code_id,'710',t.raw_value,null)) dianya,
                 max(decode(t.var_code_id,'309',t.raw_value,null)) xinhao,
                 max(decode(t.var_code_id,'712',t.raw_value,null)) valveStatus
                from (select mb.date_time,mb.tariff,mb.var_code_id,mb.raw_value,m.id 
                        from met_value_base mb
                        inner join sys_meter m on m.id=mb.meter_id 
                        where mb.var_code_id in (308,710,309,712) and mb.data_type =88 and m.meter_serial_num='331819102097') t
                group by t.date_time,t.tariff,t.id )ab
)ds where rn=1 ;

/*单个表基本客户信息、最新上报用量*/
/*单个表最新上报电池电压、信号强度、阀门状态*/
select aa.areaname,aa.customer_name,aa.id_number,aa.phone1,aa.customer_address,aa.meter_serial_num,aa.meterTime1,aa.useNum,
       ab.meterTime2,ab.dianya,ab.xinhao,ab.valveStatus
 from
(select ds1.meter_id,a.areaname,c.customer_name,c.id_number,c.phone1,c.customer_address,ds1.meter_serial_num,
       case when length(ds1.date_time_min) = 14 then
       to_char(to_date(ds1.date_time_min,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
       else '0000-00-00 00:00:00' end as meterTime1,
       ds1.consu as useNum
 from (select * from(
                    select ROW_NUMBER() over(partition by aa.meter_id order by aa.date_time_min desc) as rn,aa.*
                    from (select t.meter_id, t.date_time,t.tariff,sm.meter_serial_num,sm.customer_id,sm.terminal_id,
                                 max(decode(t.var_code_id,'308',t.raw_value,null)) date_time_min,
                                 max(decode(t.var_code_id,'143',t.raw_value,null)) consu
                            from met_value t inner join sys_meter sm on sm.id=t.meter_id
                            where t.var_code_id in (308, 143)and t.DATA_TYPE = 89 and sm.meter_serial_num='331819102097'
                           group by t.meter_id, t.date_time,t.tariff,sm.meter_serial_num,sm.customer_id,sm.terminal_id) aa
                   where aa.date_time_min is not null and aa.consu is not null) bb
           where bb.rn = 1
  ) ds1 
  inner join sys_cust c on c.id=ds1.customer_id
  inner join sys_terminal ter on ter.id=ds1.terminal_id
  inner join sys_area a on a.id=ter.parent_id)aa
inner join
(select ds.meter_id,case when length(ds.date_time_min) = 14 then
       to_char(to_date(ds.date_time_min,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') else '0000-00-00 00:00:00' end as meterTime2,
       ds.dianya,ds.xinhao,ds.valveStatus
from (select ROW_NUMBER() over(partition by ab.meter_id order by ab.date_time_min desc) as rn,ab.*
         from (select t.date_time,t.tariff,t.id as meter_id,
                 max(decode(t.var_code_id,'308',t.raw_value,null)) date_time_min,
                 max(decode(t.var_code_id,'710',t.raw_value,null)) dianya,
                 max(decode(t.var_code_id,'309',t.raw_value,null)) xinhao,
                 max(decode(t.var_code_id,'712',t.raw_value,null)) valveStatus
                from (select mb.date_time,mb.tariff,mb.var_code_id,mb.raw_value,m.id 
                        from met_value_base mb
                        inner join sys_meter m on m.id=mb.meter_id 
                        where mb.var_code_id in (308,710,309,712) and mb.data_type =88 and m.meter_serial_num='331819102097') t
                group by t.date_time,t.tariff,t.id )ab
)ds where rn=1)ab on aa.meter_id=ab.meter_id;