练练手，一句SQL实现输出九九乘法表

nyfor · 发表于 2007-4-25 20:38

有兴趣的可以试试

junsansi · 发表于 2007-4-25 21:29

哈哈，有点儿意思，俺来一个笨方法实现的~~~~

select r1 || '*' || r1 || '=' || r1 * r1 A,
   decode(r2, '', '', r2 || '*' || r1 || '=' || r2 * r1) b,
   decode(r3, '', '', r3 || '*' || r1 || '=' || r3 * r1) C,
   decode(r4, '', '', r4 || '*' || r1 || '=' || r4 * r1) D,
   decode(r5, '', '', r5 || '*' || r1 || '=' || r5 * r1) E,
   decode(r6, '', '', r6 || '*' || r1 || '=' || r6 * r1) F,
   decode(r7, '', '', r7 || '*' || r1 || '=' || r7 * r1) G,
   decode(r8, '', '', r8 || '*' || r1 || '=' || r8 * r1) H,
   decode(r9, '', '', r9 || '*' || r1 || '=' || r9 * r1) I
from (select level r1,
            lag(level, 1) over(order by level) r2,
            lag(level, 2) over(order by level) r3,
            lag(level, 3) over(order by level) r4,
            lag(level, 4) over(order by level) r5,
            lag(level, 5) over(order by level) r6,
            lag(level, 6) over(order by level) r7,
            lag(level, 7) over(order by level) r8,
            lag(level, 8) over(order by level) r9
      from dual
      connect by level < 10)

eworm · 发表于 2007-4-26 07:59

呵呵，不错不错

nyfor · 发表于 2007-4-26 09:10

不错! 虽然这样的SQL语句实现出来也没什么意义, 但可以训练你的SQL能力.
希望有更多的方法实现...

iooyoo · 发表于 2007-4-26 09:47

想了很久
不知道这个怎么样：
[php]
select rn, ltrim(max(sys_connect_by_path(product, ',')), ',') product
from
(
  select rn, product, min(product) over(partition by rn) product_min
  ,(row_number() over(order by rn, product)) + (dense_rank() over(order by rn)) numId
  from
  (
select b.rn, a.rn || '*' || b.rn || '=' || a.rn * b.rn product
from (select rownum rn from all_objects where rownum <= 9) a,
(select rownum rn from all_objects where rownum <= 9) b
where a.rn <= b.rn
order by b.rn, product
  )
)
start with product = product_min
connect by numId - 1 = prior numId
group by rn
order by rn

[/php]

nyfor · 发表于 2007-4-26 09:56

嗯,也不错!
快接近最终结果了:
1*1=1
2*1=2 2*2=4
3*1=3 3*2=6 3*3=9
.....

junsansi · 发表于 2007-4-26 10:05

最初由 iooyoo 发布
[B]想了很久
不知道这个怎么样：
[php]
select rn, ltrim(max(sys_connect_by_path(product, ',')), ',') product
from
(
  select rn, product, min(product) over(partition by rn) product_min
  ,(row_number() over(order by rn, product)) + (dense_rank() over(order by rn)) numId
  from
  (
select b.rn, a.rn || '*' || b.rn || '=' || a.rn * b.rn product
from (select rownum rn from all_objects where rownum <= 9) a,
(select rownum rn from all_objects where rownum <= 9) b
where a.rn <= b.rn
order by b.rn, product
  )
)
start with product = product_min
connect by numId - 1 = prior numId
group by rn
order by rn

[/php] [/B]

这个强，学习~~~

iooyoo · 发表于 2007-4-26 10:06

最初由 nyfor 发布
[B]嗯,也不错!
快接近最终结果了:
1*1=1
2*1=2 2*2=4
3*1=3 3*2=6 3*3=9
..... [/B]

最后有个order by rn
加了(1*1=)，不知道怎么拆开 1*2=2, 2*2=4 成两列

hit_lip01 · 发表于 2007-4-26 11:04

都是强人！！

nyfor · 发表于 2007-4-26 11:21

最初由 iooyoo 发布
[B]

最后有个order by rn
加了(1*1=)，不知道怎么拆开 1*2=2, 2*2=4 成两列 [/B]

不用拆, 这样就行了, 不错不错!

[精华] 练练手，一句SQL实现输出九九乘法表

浏览过的版块