oracle 分页查询和分组查询

分页查询

SELECT TB.EQUIPNAME,TA.equipid,TA.equipvoltage,
       TA.equipelectricity,TA.equippower,TA.equipstatus
  from (select ROWNUM RN,
               A.equipid,A.equipvoltage,A.equipelectricity,A.equippower,A.equipstatus
          from (select t.equipid,
                       t.equipvoltage,
                       t.equipelectricity,
                       t.equippower,
                       t.equipstatus
                  from L_OLI_ElectricalLoad_Data t
                 where equipAID = '8A9E426E1130'
                   and currenttime =
                       (select max(currenttime)
                          from L_OLI_ElectricalLoad_Data
                         where equipAID = '8A9E426E1130')
                   and ROWNUM <= 3) A) TA,
       (select distinct B.equipType, B.EQUIPNAME
          from L_OLI_Electrical_Archives B) TB
 where TA.equipId = TB.equipType
   and TA.RN >= 1
 ORDER BY TA.equipId
按时间分组统计数据
--按小时统计
select b.dt xDate, nvl(a.ct, 0) xValue
  from (select trunc(currenttime, 'hh24') dt, MAX(ANALYSERELECTRICITY) ct
          from oli_monitor_data
         group by trunc(currenttime, 'hh24')) a,
       (select trunc(TO_DATE('2018-11-23', 'yyyy/MM/dd'), 'DD') + (level - 1) / 24 dt
          from dual
        connect by level < 25) b
 where a.dt(+) = b.dt
 order by b.dt

–按天统计

SELECT TO_CHAR(T.currenttime, 'YYYY-MM-DD') TIME, COUNT(*) COUNT
  FROM yure_data T
 GROUP BY TO_CHAR(T.currenttime, 'YYYY-MM-DD')
 ORDER BY TO_CHAR(T.currenttime, 'YYYY-MM-DD') ASC NULLS LAST

–按自然周的日期统计

select to_char(next_day(t.currenttime+15/24 - 7,2),'YYYY-MM-DD') AS xdata,sum(1) as xvalue
from yure_data t
group by to_char(next_day(t.currenttime+15/24 - 7,2),'YYYY-MM-DD')
ORDER BY xdata;

–按自然周的第几周统计

SELECT TO_CHAR(T.currenttime, 'YYYY') YEAR,
       TO_CHAR(T.currenttime, 'IW') TIME,
       COUNT(*) COUNT
  FROM yure_data T
 GROUP BY TO_CHAR(T.currenttime, 'IW'), TO_CHAR(T.currenttime, 'YYYY')
 ORDER BY TO_CHAR(T.currenttime, 'YYYY'),
          TO_CHAR(T.currenttime, 'IW') ASC NULLS LAST

–按自然月统计

SELECT TO_CHAR(T.currenttime, 'YYYY-MM') TIME, COUNT(*) COUNT
  FROM yure_data T
  where  currenttime between to_date('2018-01', 'yyyy-mm') and
                  to_date('2018-12', 'yyyy-mm')
 GROUP BY TO_CHAR(T.currenttime, 'YYYY-MM')
 ORDER BY TO_CHAR(T.currenttime, 'YYYY-MM') ASC NULLS LAST

–按季度统计

SELECT TO_CHAR(T.currenttime, 'YYYY') YEAR,
       TO_CHAR(T.currenttime, 'Q') TIME,
       COUNT(*) COUNT
  FROM yure_data T
 GROUP BY TO_CHAR(T.currenttime, 'Q'), TO_CHAR(T.currenttime, 'YYYY')
 ORDER BY TO_CHAR(T.currenttime, 'YYYY'),
          TO_CHAR(T.currenttime, 'Q') ASC NULLS LAST

–按年度统计

SELECT TO_CHAR(T.currenttime, 'YYYY') YEAR, COUNT(*) COUNT
  FROM yure_data T
 GROUP BY TO_CHAR(T.currenttime, 'YYYY')
 ORDER BY TO_CHAR(T.currenttime, 'YYYY') ASC NULLS LAST

每隔15分钟统计一次

select        to_char(currenttime, 'hh24')||':'||floor(to_char(currenttime, 'mi')/15)*15 tm, count(*)
from        yure_data
where currenttime+0>to_date('20181125','yyyymmdd') and currenttime+0<to_date('20181126','yyyymmdd')
group by to_char(currenttime, 'hh24')||':'||floor(to_char(currenttime, 'mi')/15)*15
order by to_char(currenttime, 'hh24')||':'||floor(to_char(currenttime, 'mi')/15)*15 asc

按月分组统计数据，数据为空补零

select to_char(currenttime, 'yyyy-mm') xname, (max(t.cumulativeheat) - min(t.cumulativeheat)) / 9 xvalue
  from S_WHR t
  where TO_CHAR(t.currenttime, 'YYYY-MM-DD') like '2018-11%'
  ---where t.currenttime + 0 >= to_date('2018-11-01', 'yyyy-mm-dd')
  ---and t.currenttime - 1 < to_date('2018-11-30', 'yyyy-mm-dd')
 group by to_char(currenttime, 'yyyy-mm')
 order by to_char(currenttime, 'yyyy-mm');

第二种写法

select t2.datevalue xname,nvl(t1.tvalue, 0) xvalue
  from (select (max(t.cumulativeheat) - min(t.cumulativeheat)) / 9 tvalue, TO_CHAR(t.currenttime, 'yyyy-mm') timevalue
          from S_WHR t
         where TO_CHAR(t.currenttime, 'YYYY-MM-DD') like '2018%'
         group by TO_CHAR(t.currenttime, 'yyyy-mm')) t1,
       (select '2018-' || lpad(level, 2, 0) datevalue
          from dual
        connect by level < 13) t2
 where t1.timevalue(+) = t2.datevalue
 order by t2.datevalue

两个日期相差的天时分秒

SELECT TD, TH, TM, ROUND((TT - TM) * 60) AS TS
  FROM (SELECT ESTIME,
               EETIME,
               TD,
               TH,
               TRUNC((TT - TH) * 60) AS TM,
               (TT - TH) * 60 AS TT
          FROM (SELECT ESTIME,
                       EETIME,
                       TD,
                       TRUNC((TT - TD) * 24) AS TH,
                       (TT - TD) * 24 AS TT
                  FROM (SELECT TO_NUMBER(EETIME - ESTIME) AS TT,
                               TRUNC(TO_NUMBER(EETIME - ESTIME)) AS TD,
                               ESTIME,
                               EETIME
                          FROM (SELECT min(currenttime) + 0 AS ESTIME,
                                       max(currenttime) + 0 AS EETIME
                                  FROM yure_data))));

分组，sum，sum case when

select ta.currenttime,
       sum(ta.wh) total,
       sum(case
             when ta.equipId = 'ammeter012' or ta.equipid = 'ammeter013' then
              wh
           end) vo,
       (sum(ta.wh) / sum(case
                           when ta.equipId = 'ammeter012' or ta.equipid = 'ammeter013' then
                            wh
                         end)) xvalue
  from (select currenttime, equipid, wh
          from l_acc_ammeter t
         where TO_CHAR(currenttime, 'YYYY-MM-DD') like '2019-01-22%'
         group by t.currenttime, t.equipid, wh
         order by currenttime asc) ta
 group by ta.currenttime
 order by ta.currenttime asc

分页查询和表关联

 SELECT Tg.EQUIPNAME,Tg.equipid,Tg.equipvoltage,
        Tg.equipelectricity,Tg.equippower,Tg.equipstatus
   from (select ROWNUM RN,
                tf.equipname,te.equipid,te.equipaid,te.equipvoltage,
                te.equipelectricity,te.equippower,te.equipstatus,te.equipusepower
           from l_oli_electricalload_data te,
                (select tb.equipid, tc.equipname
                   from (select equipid, count(1)
                           from (select t.equipid,t.equipaid,t.equipvoltage,t.equipelectricity,
                                        t.equippower,t.equipstatus,t.equipusepower, t.currenttime
                                   from L_OLI_ElectricalLoad_Data t
                                  where currenttime + 0 >=to_date('2019-02-28', 'yyyy-mm-dd')
                                    and t.equipaid = '8A9E426E1120'
                                  order by currenttime desc) ta
                          group by ta.equipid) tb,
                        (select distinct t.equiptype, t.equipname
                           from L_OLI_Electrical_Archives t
                          order by to_number(t.equiptype)) tc
                  where tb.equipid = tc.equiptype) tf
          where te.equipid = tf.equipid
            and currenttime =
                (select max(currenttime) from l_oli_electricalload_data)
            and te.equipaid = '8A9E426E1120'
            and ROWNUM <= 3) Tg
  where Tg.RN >= 1
  ORDER BY to_number(Tg.equipId) asc