时间: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?
读者可以试着自己思考写下,再往下翻... 如有不同解题方式,大家一起交流。
解答:
- <span style="color: #0000ff;">WITH</span> T <span style="color: #0000ff;">AS</span><span style="color: #000000;">
- (</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
- </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"> DUAL
- </span><span style="color: #0000ff;">UNION</span> <span style="color: #808080;">ALL</span>
- <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
- </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"> DUAL
- </span><span style="color: #0000ff;">UNION</span> <span style="color: #808080;">ALL</span>
- <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
- </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"> DUAL
- </span><span style="color: #0000ff;">UNION</span> <span style="color: #808080;">ALL</span>
- <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
- </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"> DUAL
- </span><span style="color: #0000ff;">UNION</span> <span style="color: #808080;">ALL</span>
- <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
- </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"> DUAL
- </span><span style="color: #0000ff;">UNION</span> <span style="color: #808080;">ALL</span>
- <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
- </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"> DUAL),
- tt (id, num, result) </span><span style="color: #0000ff;">as</span><span style="color: #000000;"> (
- </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>
- <span style="color: #0000ff;">union</span> <span style="color: #808080;">all</span>
- <span style="color: #0000ff;">select</span> t.id ,t.num,tt.result<span style="color: #808080;">+</span><span style="color: #000000;">tt.num
- </span><span style="color: #0000ff;">from</span><span style="color: #000000;"> tt,t
- </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
- )
- </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