第二届SQL挑战赛获奖名单公布！祝各位墨友中秋快乐

各位墨友们，久等啦！继上一季SQL挑战赛的热烈反响之后，我们又迎来了第二期比赛（点击此处立即回顾）。这次，我们看到了更多热情的参与者和令人印象深刻的作品。本次活动共收到近100份作品，感谢大家的积极参与。

经过紧张的评审环节，最终forever、DarkAthena、锁钥、大大刺猬四位挑战者获得优秀奖，另有60名用户获得合格奖，值得一提的是，在本次的比赛中，技术专家广州-老虎刘 更是创新性地给出八种答题方式，令人眼前一亮，特被评为“特别奖”。接下来一同和小编欣赏这些具有创造性的优秀作品吧！

特别奖：广州-老虎刘

专家点评：
其中的方法1，方法2，方法3，方法7与前边参与者的方法类似，这里不再赘述。方法4，方法5和方法6，使用了ORACLE中的特有语法，方法8则巧妙地利用count(amount) over (order by seqno)将amount列上空值行，与相应的非空行编成同一分组，为后续使用max,min等多种方法获取结果创造了有利条件。
总之，作者的8种方法，让人眼界大开。虽然从发贴时间上，其不是靠前的，但鉴于其提供了众多方法，建议授予特别奖。

-- 作品一：last_value + ignore nulls 分析函数 , 写法最简单
select seqno,amount
     ,last_value(amount) ignore nulls over(order by seqno) as amount_all
from  test_gen a
order by seqno;

-- 作品二：nvl+lag ignore nulls 分析函数
select seqno,amount
      ,nvl(amount,lag(amount) ignore nulls over(order by seqno)) as amount_all
from  test_gen a
order by seqno;

-- 作品三： 标量子查询, 12c+版本 需要(seqno,amount)索引配合 
select seqno,amount
     ,(select b.amount from test_gen b where b.seqno<=a.seqno and b.amount is not null order by b.seqno desc fetch first 1 rows only) as amount_all
from  test_gen a
order by seqno;

-- 作品四：： outer apply,12c+版本 需要(seqno,amount)索引配合 
select a.seqno,a.amount
      ,x.amount as amount_all
from  test_gen a
outer apply
(select * from test_gen b   
             where a.seqno>=b.seqno and b.amount is not null 
             order by b.seqno desc
             fetch first 1 rows only
             )x 
order by 1;

-- 作品五： match_recognize, 12c+ 版本(oracle特有语法)
select seqno,amount,amount_all
from
  test_gen a
match_recognize(
order by seqno
measures
   first(amount) as amount_all
all rows per match
 pattern(a b*)
 define b as amount is null
);

-- 作品六：model语法
select seqno,amount as amount_all
from test_gen_2
model return updated rows
  dimension by (row_number() OVER (ORDER BY seqno) rn)
  measures( seqno,amount )
  rules (
        amount[any] = decode(amount[cv(rn)], null, NVL(amount[cv(rn) - 1], 0), amount[cv(rn)])
       )
order by seqno;

-- 作品七： 递归写法(写法不是太严谨,把seqno当连续序列使用)
with cte1(seqno,amount,amount_all) as 
(
    select seqno, amount, amount as amount_all
    from test_gen
    where amount is not null
    union all
    select t.seqno, t.amount, nvl(t.amount, x.amount_all) as amount_all
    from test_gen t,cte1 x
    where t.seqno = x.seqno + 1 and t.amount is null
)
select seqno, amount, amount_all
from cte1
order by seqno;

-- 作品八： count+(min/max/first_value 任选一种) 两次分析函数写法
with mid1 as
(
select a.*
      ,count(amount) over (order by seqno) as group# 
from test_gen_2 a
)select a.seqno,a.amount
    ,first_value(amount) over (partition by group#) amount_all_1
    ,max(amount) over (partition by group#) amount_all_2
    ,min(amount) over (partition by group#) amount_all_3
from mid1 a
order by seqno;

恭喜特别奖获得者老虎刘，将获得由墨天轮数据特别颁发的全套新周边以及 《数据库简史》签名版实体书一本！

优秀奖

• forever

专家点评：
只使用一次嵌套完成SQL，代码异常简洁。该写法是利用Oracle提供的分析函数LAG，以及附带IGNOR NULLS选项的处理的。结果及处理方法均满足题目要求。

SELECT SEQNO,
       AMOUNT,
       COALESCE(AMOUNT, LAG(AMOUNT IGNORE NULLS) OVER(ORDER BY SEQNO)) AS AMOUNT_ALL
  FROM test_gen_2;

• DarkAthena

专家点评：
使用递归调用别具一格，且代码兼容多种数据库，也是可用于Oracle库环境中的回答中，第一个成功完成的。

with  recursive
t1(seqno,amount,amount_all,lvl) as (
select t.seqno,amount,amount amount_all,1 lvl from test_gen t where amount is not null
union all
select t.seqno,t.amount,t1.amount_all,lvl+1 from test_gen t,t1 where t.amount is null and t.seqno=t1.seqno+1
)
 select seqno,amount,amount_all from t1 order by seqno;

该SQL在ORACLE中运行只需要去掉“recursive”即可

• 锁钥

专家点评：
该SQL利用Oracle提供的LAST_VALUE的分析函数，以及附带的IGNORE NULLS选项和开窗子句（但该开窗子句可以省略），完成相关处理。运行结果和处理方法满足题目要求。是可用于Oracle库环境中的回答中，第三个成功完成的。

SELECT seqno,
       amount,
       LAST_VALUE(amount IGNORE NULLS) OVER(ORDER BY seqno ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS amount_all
  FROM test_gen;

• 大大刺猬

专家点评：
第一个提交正确答案，使用构造替换方法。

SELECT 
    seqno, 
    amount, 
    CAST(IF(amount is null, @amount_all, @amount_all := amount) AS CHAR) AS amount_all
FROM 
    (SELECT seqno, 
            IF(amount = null, @prev_amount, amount) AS amount,
            @prev_amount := IF(amount = null, @prev_amount, amount) 
     FROM test_gen, (SELECT @prev_amount := NULL) AS vars
     ORDER BY seqno
    ) AS t
ORDER BY seqno;

以上获得优秀奖的挑战者均可以获得盖总亲笔签名的《数据库简史》实体书籍一本！

合格奖名单

恭喜以上合格奖的获得者，均可获得100墨值/人的奖励！

请获得实物奖品的作者尽快联系小墨（VX：modb666）提交收货信息领取您的奖品，此外合格奖用户的墨值奖励将在2-3个工作日内到达账户，请注意查收哟～

原始数据如图1所示，希望通过一条SQL查出虚拟列AMOUNT_ALL，该列以SEQNO的顺序并根据AMOUNT字段的值填充，如果AMOUNT有值则虚拟列等于该条记录中AMOUNT的值，如果AMOUNT的值为NULL，则该虚拟列为上一条AMOUNT不为NULL的值，输出要求如图2所示：

原始数据如下：

drop table test_gen purge;

create table test_gen as
(
select 1 as seqno,1 as amount from dual
union all
select 2 as seqno,null as amount from dual
union all
select 3 as seqno,null as amount from dual
union all
select 4 as seqno,null as amount from dual
union all
select 5 as seqno,2 as amount from dual
union all
select 6 as seqno,null as amount from dual
union all
select 7 as seqno,null as amount from dual
union all
select 8 as seqno,3 as amount from dual
union all
select 9 as seqno,null as amount from dual
union all
select 10 as seqno,null as amount from dual
union all
select 11 as seqno,5 as amount from dual
);

随着墨天轮SQL挑战赛第二期的圆满结束，我们见证了一场精彩的数据技能较量，再次感谢所有参与者，让我们期待未来更多的社区活动，一同前行，探索更多的可能！