oracle 很复杂的sql语句出错,请教!谢谢

发布网友发布时间：2022-05-02 09:35

共4个回答

懂视网时间：2022-05-02 13:56

一、over()分析函数

　　分组查前几条：select * from test t where (select count(*) from test a where t.type=a.type and t.scope>a.scope)<2；

　　--rank()/dense_rank() over(partition by ...order by ...)
　　select * from(select t.*,rank() over(partition by t.type order by t.scope ) a from TEST t) a where a.a<3

--dense_rank()分级连续排序
select t.*,dense_rank() over(partition by t.type order by t.scope)a from test t
--rank()分级跳跃排序
select t.*,rank() over(partition by t.type order by t.scope)a from test t

select * from Test t where 2>(select count(*) from Test a where t.type=a.type and t.scope>a.scope)
select t.* from Test t,(select a.type,max(a.scope) scope from TEST a group by a.type) d where t.type=d.type and t.scope=d.scope

--笛卡尔乘积
select * from Test t,Test a
select t.* from Test t,(select a.type,max(a.scope) maxscope,min(a.scope) minscope from TEST a group by a.type) d where t.type=d.type and t.scope=d.scope

　　--
select t.*,d.maxscope-t.scope maxscope,t.scope-d.minscope minscope
from Test t,
       (select a.type, max(a.scope) maxscope, min(a.scope) minscope
          from TEST a
         group by a.type) d
where t.type = d.type

--min()/max() over(partition by ...)
select t.*,
       nvl(max(t.scope) over(partition by t.type), 0) - t.scope maxscope,
       t.scope - nvl(min(t.scope) over(partition by t.type), 0) minscope
from test t

--lead()/lag() over(partition by ... order by ...)
select t.*,lead(t.scope,1,0)over(partition by t.type order by t.scope) a--同组后一个
       from test t
select t.*,lag(t.scope,1,0)over(partition by t.type order by t.scope) a--同组前一个
       from test t

select t.*,
       first_value(t.scope) over(partition by t.type) first_sal,
       last_value(t.scope) over(partition by t.type) last_sal,
       sum(t.scope) over(partition by t.type) sum_sal,
       avg(t.scope) over(partition by t.type) avg_sal,
       count(t.scope) over(partition by t.type) count_num,
       row_number() over(partition by t.type order by t.scope) row_num
from test t

--注：带order by子句的方法说明在使用该方法的时候必须要带order by

oracle复杂查询是sql

标签：strong -- type over 复杂连续 order sel 必须

热心网友时间：2022-05-02 11:04

你原来的CASE WHEN TO_CHAR( a.tm,' HH24') BETWEEN 2 AND 8 THEN drp END
只取出的是小时，没有日期，统计的也是符合条件(a.TM >=to_date('2013-05-09 8','yyyy-MM-dd HH24'))的所有数据
你应该用 CASE WHEN a.tm BETWEEN trunc(sysdate,'dd') +1+2/24 AND trunc(sysdate,'dd')+1 +8/24 THEN drp END

热心网友时间：2022-05-02 12:22

不用那么复杂吧你别管时间过没过就这四个时间段嘛把你那个表的时间按照这个时间段分好按照时间汇总就可以了嘛不管什么时候查询都没有问题

热心网友时间：2022-05-02 13:57

SELECT
TO_CHAR(TM, 'HH24:MI:SS') AS hms,
CASE WHEN TO_CHAR( a.TM,'HH24') BETWEEN 8 AND 14 THEN 1 END AS Data8To14,
CASE WHEN TO_CHAR( a.TM,'HH24') BETWEEN 14 AND 20 THEN 1 END AS Data14To20,
CASE WHEN TO_CHAR( a.TM,'HH24') BETWEEN 20 AND TO_CHAR(a.TM,'HH24') THEN 1 END AS Data20To2,
CASE WHEN TO_CHAR( a.tm,' HH24') BETWEEN 2 AND 8 THEN 1 END AS Data2To8
FROM
(SELECT TRUNC(SYSDATE) + (ROWNUM-1) / 24 AS TM FROM user_objects where rownum <= 24) a;

你那个时间判断的代码，问题不少啊。
我按照你的写法， 1天24小时的模拟了一下。 0点到1点的数据丢了。
因为那个 BETWEEN 20 AND 2 是不合理的啊......