超有难度的sql，200分奉上！

2011-03-09 07:21:39 284 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

数据开放最全sql面试合集(leetcode) 数据分析，数据开发常见sql面试题合集，大厂常见sql面试题，连续登录，TopN等常见面试题阅读详情

数据很简单:
max value
8 0
9 0
10 17
11 5
12 0

max值自8开始，逐行递增1
每行的value不允许超过max值，超过部分计入下行，以此类推
要求结果为
8 0
9 0
10 10
11 11
12 1

说明，不一定仅此五行，后面的可以按max规律再加

...全文

慕课软件质量保证与测试(习题集) 慕课金陵科技学院.软件质量保证与测试.期末复习.习题汇总0 总目录1 绪论1.1 软件质量保证与测试的产生与发展1.2 软件缺陷,软件错误,软件故障1.3 软件质量保证与测试的意义,原则和挑战1.4 单元测试1.5 课后作业2 软件测试策略2.1 软件测试的模型,过程和生命周期2.2 软件测试的方法和技术2.3 单元测试2.4 课后作业3 黑盒测试3.1 黑盒测试概述3.2 等价类划分测试3.3 边界值测试3.4 错误推测法3.5 判定表驱动法3.6 因果图法3.7 场景法3.8 正交实验法3.9 单元测试阅读详情

浪尖赏花

Oracle 高级技术 3,499 成员 18,710 内容

+ 关注 + 加入社区

切换为时间正序

浪尖赏花 2011-03-10

打赏
举报

今天晚上结贴，如果大家想挑战下，有新的方法的话，我可以另外开贴给分

浪尖赏花 2011-03-10

打赏
举报

[Quote=引用 10 楼 minitoy 的回复:]
你之前的统计不能变通下来实现需求么？
[/Quote]
我是做的年假统计，给我的数据，只有入职日期、休假的明细表。
年假嘛，大家都懂的，有时间限制的，过时不休视为自动放弃，我需要统计的是每个年周期已休记录数、可休天数。兄台有什么好的办法？

比如我是2008-12-01入职的，到2009-12-01开始我可以休8天，截止日期到2010-11-30日，
2009-12-01至2010-11-30，我有9天的年假，截止日期到2011-11-30日，
以此类推
除了第一年要满一年外外，以后每年的年假可休时间都是按照已过月份/12*年周期年假天数

tangren 2011-03-10

打赏
举报

[Quote=引用 9 楼 lihui_shine 的回复:]

修改了一下^_^
SQL code
select max,case when tmp>max then max else tmp end val from (
select max,v,tmp from
(select max,val val from t order by max)
model
dimension by (max)
measures (val v,……
[/Quote]
优化得不错，我写复杂了哈~~~

minitoy 2011-03-10

打赏
举报

你之前的统计不能变通下来实现需求么？

浪尖赏花 2011-03-10

打赏
举报

修改了一下^_^

select max,case when tmp>max then max else tmp end val from (

  select max,v,tmp from 

    (select max,val val from t order by max)

  model

  dimension by (max)

  measures (val v,max m,0 as tmp)

    rules(

      tmp[any]= case when v[cv()-1] is null then

          v[cv()]

      else

        case when tmp[cv()-1]>m[cv()-1] then

          tmp[cv()-1]-m[cv()-1]+v[cv()]

        else

          v[cv()]

        end

      end

    )

)

还有没有其他sql方法呢

浪尖赏花 2011-03-10

打赏
举报

[Quote=引用 7 楼 tangren 的回复:]
SQL code
--试一下SQL解决，表名为t
select max,decode(tmp1,-1,tmp,max) val from (
select max,v,tmp,tmp1 from
(select max,val val from t order by max)
model
dimension by (max)
measures (val v,……
[/Quote]
tangren大大厉害，基本上效果达到了，我刚刚测试了，基本上OK，有一组数据有点问题，tangren大大是不是有个别情况没有考虑到呢^_^
我先来消化消化

with t as (

select 8 max,0 val from dual

union all

select 9,0 from dual

union all

select 10,17 from dual

union all

select 11,12 from dual

union all

select 12,0 from dual

union all

select 13,18 from dual

union all

select 14,0 from dual

)

tangren 2011-03-10

打赏
举报

--试一下SQL解决，表名为t

select max,decode(tmp1,-1,tmp,max) val from (

  select max,v,tmp,tmp1 from 

    (select max,val val from t order by max)

  model

  dimension by (max)

  measures (val v,max m,0 as tmp,0 as tmp1)

    rules(

      tmp[any]= case when v[cv()-1] is null then

        case when v[cv()]>m[cv()] then

          v[cv()]-m[cv()]

        else

          v[cv()]

        end

      else

        case when tmp[cv()-1]+v[cv()]>m[cv()] then

          tmp[cv()-1]+v[cv()]-m[cv()]

        else

          tmp[cv()-1]+v[cv()]

        end

      end,

      tmp1[any]= case when v[cv()-1] is null then

        case when v[cv()]>m[cv()] then 1 else -1 end

      else

        case when tmp[cv()-1]+v[cv()]>m[cv()] then 1 else -1 end

      end

    )

)

浪尖赏花 2011-03-10

打赏
举报

感谢zhuomingwang、gelyon大大的回复，用存储过程、匿名块等方式的确可以解决我们这个问题！
但是有一个问题，我的数据源不是直接从表里的读取出来的，数据源本身就是统计出来的，用sql的方式能否实现。如果实在实现不了，我该如何解决？

gelyon 2011-03-09

打赏
举报



--再修改下，假设你value值很大，导致你max没有那么多需要分摊，自动新增：



--原数据：

--注意没有max=16的

select * from tmp;

MAX	  Value

-------------

8	  0

9	  0

10   	  17

11	  5

12	  0

13	  26

14	  13

15	  12





--执行如下匿名块：

declare

   cnt number;

   val number;

begin

  select count(*) into cnt from tmp where value>max;

  while cnt>0 loop

      for rs in (select * from tmp where value>max ) loop

          val := rs.value-rs.max;

          update tmp set value=max where max=rs.max;

          update tmp set value=value+val where max=rs.max+1;

          if sql%rowcount!=1 then

             insert into tmp(max,value) values(rs.max+1,val);  --新增

          end if;

          commit;

      end loop;

      select count(*) into cnt from tmp where value>max;

  end loop;

end;



--结果:

select * from tmp;

MAX	  Value

-----------------

8	  0

9	  0

10	  10

11	  11

12	  1

13	  13

14	  14

15	  15

16	  9    --新增的分摊数据

gelyon 2011-03-09

打赏
举报



--初始数据：

select * from tmp;



MAX      Value

------------------

8         0

9         0

10        17

11        5

12        0

13        26

14        13

15        12

16        3





--执行如下匿名PLSQL块：



declare

   cnt number;

   val number;

begin

   select count(*) into cnt from tmp where value>max;

   while cnt>0 loop

         for rs in (select * from tmp where value>max ) loop

             val := rs.value-rs.max;

             update tmp set value=max where max=rs.max;

             update tmp set value=value+val where max=rs.max+1;

             commit;

         end loop;

         select count(*) into cnt from tmp where value>max;

   end loop;

end;



--结果：

select * from tmp;



MAX      Value

-----------------

8         0

9         0

10        10

11        11

12        1

13        13

14        14

15        15

16        12

真的不知道取什么名 2011-03-09

打赏
举报



/*

抛砖引玉吧

缺陷m列(即你的max列只能是从8开始按1递增



楼主执行这个过程前做好备份

*/

create or replace procedure scott.update_pro

as

n_flag number;

n_cnt number;

n_m test.m%type;

n_v test.v%type;

begin

n_flag:=0;

select count(*) into n_cnt from test;

for i in 8..7+n_cnt loop

select m,v into n_m,n_v from test where m=i;

if n_v+n_flag<=n_m then

update test set v=n_v+n_flag

where m=i;

else

update test set v=m

where m=i;

n_flag:=n_v+n_flag-n_m;

end if;

end loop;

commit;

exception

   when others then

   dbms_output.put_line(sqlerrm);

end;



--执行过程前

select * from test;

     M          V

------ ----------

     8          0

     9          0

    10         17

    11          5

    12          0



--执行过程后

 select * from test;



         M          V

---------- ----------

         8          0

         9          0

        10         10

        11         11

        12          1

njlywy 2011-03-09

打赏
举报

坐等达人…

luoyoumou 2011-03-09

打赏
举报

-- 用ＳＱＬ很难，游标处理是最好的啦！

超有难度的sql，200分奉上！

相关推荐