当前位置:Gxlcms > 数据库问题 > SQL每日一题(20200509)

SQL每日一题(20200509)

时间:2021-07-01 10:21:17 帮助过:21人阅读

题目

有如下一张表T

技术图片

 

想要得到如下结果:

技术图片

解释:T表中的Result列的第1行始终保持为1,后面的数据行=上一行的结果+上一行的NUM值,例如ID为2的Result为6=1+5,ID为3的Result为9=6+3,以此类推。
该如何写这个SQL?

 

读者可以试着自己思考写下,再往下翻... 如有不同解题方式,大家一起交流。

 

 

 

 

 

 

解答:

  • with递归的方式(oracle11g 支持with递归)
  1. <span style="color: #0000ff;">WITH</span> T <span style="color: #0000ff;">AS</span><span style="color: #000000;">
  2. (</span><span style="color: #0000ff;">SELECT</span> <span style="color: #800000; font-weight: bold;">1</span> ID, <span style="color: #800000; font-weight: bold;">5</span><span style="color: #000000;"> NUM
  3. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"> DUAL
  4. </span><span style="color: #0000ff;">UNION</span> <span style="color: #808080;">ALL</span>
  5. <span style="color: #0000ff;">SELECT</span> <span style="color: #800000; font-weight: bold;">2</span> ID, <span style="color: #800000; font-weight: bold;">3</span><span style="color: #000000;"> NUM
  6. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"> DUAL
  7. </span><span style="color: #0000ff;">UNION</span> <span style="color: #808080;">ALL</span>
  8. <span style="color: #0000ff;">SELECT</span> <span style="color: #800000; font-weight: bold;">3</span> ID, <span style="color: #800000; font-weight: bold;">12</span><span style="color: #000000;"> NUM
  9. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"> DUAL
  10. </span><span style="color: #0000ff;">UNION</span> <span style="color: #808080;">ALL</span>
  11. <span style="color: #0000ff;">SELECT</span> <span style="color: #800000; font-weight: bold;">4</span> ID, <span style="color: #800000; font-weight: bold;">2</span><span style="color: #000000;"> NUM
  12. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"> DUAL
  13. </span><span style="color: #0000ff;">UNION</span> <span style="color: #808080;">ALL</span>
  14. <span style="color: #0000ff;">SELECT</span> <span style="color: #800000; font-weight: bold;">5</span> ID, <span style="color: #800000; font-weight: bold;">7</span><span style="color: #000000;"> NUM
  15. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"> DUAL
  16. </span><span style="color: #0000ff;">UNION</span> <span style="color: #808080;">ALL</span>
  17. <span style="color: #0000ff;">SELECT</span> <span style="color: #800000; font-weight: bold;">6</span> ID, <span style="color: #800000; font-weight: bold;">9</span><span style="color: #000000;"> NUM
  18. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"> DUAL),
  19. tt (id, num, result) </span><span style="color: #0000ff;">as</span><span style="color: #000000;"> (
  20. </span><span style="color: #0000ff;">select</span> id,num,<span style="color: #800000; font-weight: bold;">1</span> result <span style="color: #0000ff;">from</span> t <span style="color: #0000ff;">where</span> id<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">1</span>
  21. <span style="color: #0000ff;">union</span> <span style="color: #808080;">all</span>
  22. <span style="color: #0000ff;">select</span> t.id ,t.num,tt.result<span style="color: #808080;">+</span><span style="color: #000000;">tt.num
  23. </span><span style="color: #0000ff;">from</span><span style="color: #000000;"> tt,t
  24. </span><span style="color: #0000ff;">where</span> tt.id<span style="color: #808080;">+</span><span style="color: #800000; font-weight: bold;">1</span><span style="color: #808080;">=</span><span style="color: #000000;">t.id
  25. )
  26. </span><span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> tt;<br><br>

ID   NUM   RESULT
1   5   1
2   3   6
3   12   9
4   2   21
5   7   23
6   9   30

 

 

如有其它解题思路,欢迎一起交流学习。

SQL每日一题(20200509)

标签:ade   结果   https   white   思路   original   oracle数据库   sans   oracl   

人气教程排行