<rt id="bn8ez"></rt>
<label id="bn8ez"></label>

  • <span id="bn8ez"></span>

    <label id="bn8ez"><meter id="bn8ez"></meter></label>

    Decode360's Blog

    業精于勤而荒于嬉 QQ:150355677 MSN:decode360@hotmail.com

      BlogJava :: 首頁 :: 新隨筆 :: 聯系 ::  :: 管理 ::
      302 隨筆 :: 26 文章 :: 82 評論 :: 0 Trackbacks
    ??? 網上找到的打印九九乘法表的SQL程序,關鍵的一些思路還是很有借鑒價值的:
    ?
    ?
    一、枚舉法:
    ?
    select r1 || '*' || 1 || '=' || r1 * 1 A,
    ?????? decode(r2, '', '', r2 || '*' || 2 || '=' || r2 * 2) b,
    ?????? decode(r3, '', '', r3 || '*' || 3 || '=' || r3 * 3) C,
    ?????? decode(r4, '', '', r4 || '*' || 4 || '=' || r4 * 4) D,
    ?????? decode(r5, '', '', r5 || '*' || 5 || '=' || r5 * 5) E,
    ?????? decode(r6, '', '', r6 || '*' || 6 || '=' || r6 * 6) F,
    ?????? decode(r7, '', '', r7 || '*' || 7 || '=' || r7 * 7) G,
    ?????? decode(r8, '', '', r8 || '*' || 8 || '=' || r8 * 8) H,
    ?????? decode(r9, '', '', r9 || '*' || 9 || '=' || r9 * 9) I
    ? from (select level r1,
    ?????????????? lag(level + 1, 1) over(order by level) r2,
    ?????????????? lag(level + 2, 2) over(order by level) r3,
    ?????????????? lag(level + 3, 3) over(order by level) r4,
    ?????????????? lag(level + 4, 4) over(order by level) r5,
    ?????????????? lag(level + 5, 5) over(order by level) r6,
    ?????????????? lag(level + 6, 6) over(order by level) r7,
    ?????????????? lag(level + 7, 7) over(order by level) r8,
    ?????????????? lag(level + 8, 8) over(order by level) r9
    ????????? from dual
    ??????? connect by level < 10);
    ?
    1、先用connect by列出1-9的數列
    2、用lag函數排好次序
    3、最后打印出來。
    ?
    ?
    二、高級枚舉法:
    ?
    select max(decode(a, 1, cnt)) A,
    ?????? max(decode(a, 2, cnt)) B,
    ?????? max(decode(a, 3, cnt)) C,
    ?????? max(decode(a, 4, cnt)) D,
    ?????? max(decode(a, 5, cnt)) E,
    ?????? max(decode(a, 6, cnt)) F,
    ?????? max(decode(a, 7, cnt)) G,
    ?????? max(decode(a, 8, cnt)) H,
    ?????? max(decode(a, 9, cnt)) I
    ? from (select a.rn a, b.rn b, a.rn || '*' || b.rn || '=' || a.rn * b.rn cnt
    ????????? from (select rownum rn from dual connect by rownum <= 9) a,
    ?????????????? (select rownum rn from dual connect by rownum <= 9) b
    ???????? where a.rn <= b.rn)
    ?group by b
    ?order by 1;
    ?
    1、列出1-9的數列
    2、用笛卡爾積做出各個乘積
    3、排除多于的值,并排序輸出
    ?
    ?
    三、匯總結果法:
    ?
    select max(substr(sys_connect_by_path(b.n || '*' || a.n || '=' || a.n * b.n,', '),3)) as val
    ? from (select rownum n from all_objects where rownum <= 9) a,
    ?????? (select rownum n from all_objects where rownum <= 9) b
    ?where a.n >= b.n
    ?start with b.n = 1
    connect by a.n = prior a.n
    ?????? and b.n = prior b.n + 1
    ?group by a.n
    ?order by a.n;
    ?
    1、前面和枚舉法一樣列出1-9的數列,然后用笛卡爾乘積
    2、在打印時使用sys_connect_by_path函數,打印到一個字段中
    3、上面的程序中間步驟比較省略,下面這個程序比較詳細一些
    ?
    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;
    ?
    ?
    四、牛逼的簡化SQL:
    ?
    SELECT REPLACE(REVERSE(sys_connect_by_path(REVERSE(rownum || '*' || lv || '=' ||rpad(rownum * lv, 2)),'/ ')),'/')
    ? FROM (SELECT LEVEL lv FROM dual CONNECT BY LEVEL < 10)
    ?WHERE lv = 1
    CONNECT BY lv + 1 = PRIOR lv;
    ?
    1、列出1-9序列
    2、使用本身的循環 lv + 1 = prior lv
    3、使用sys_connect_by_path函數打印
    4、具體的格式調整的方法還有很多,不列舉
    ?
    ?
    ?
    ?
    ?
    總結:要用一個字段打印一列是不太現實的,擴展性不大,要是99*99乘法表就不可能打印
    ????? sys_connect_by_path還是一個比較實用的函數。




    -The End-

    posted on 2008-09-29 11:22 decode360-3 閱讀(264) 評論(0)  編輯  收藏 所屬分類: SQL Dev
    主站蜘蛛池模板: 亚洲精品二区国产综合野狼| 国产精品免费观看久久| 一本色道久久88亚洲综合| 亚洲国产成人久久三区| 亚洲网站免费观看| 亚洲日韩中文字幕| 美丽姑娘免费观看在线观看中文版| 亚洲精品乱码久久久久66| 国产一区二区免费| 久久国产精品亚洲一区二区| 十九岁在线观看免费完整版电影| 亚洲AV无码成人专区片在线观看 | 亚洲片国产一区一级在线观看| 阿v视频免费在线观看| 亚洲精品人成无码中文毛片| xxxx日本在线播放免费不卡| 国产黄色一级毛片亚洲黄片大全| 一二三四免费观看在线视频中文版| 亚洲综合久久成人69| 插B内射18免费视频| 亚洲国产成人私人影院| 99在线热视频只有精品免费| 亚洲伊人色一综合网| 免费无码看av的网站| 特级毛片A级毛片100免费播放| 伊人久久综在合线亚洲91 | 久久www免费人成看片| 色在线亚洲视频www| 免费一级毛片正在播放| 中文字幕成人免费高清在线视频 | 午夜在线a亚洲v天堂网2019| 日本不卡免费新一二三区| eeuss影院www天堂免费| 亚洲成人中文字幕| 午夜私人影院免费体验区| 国产免费久久精品丫丫| 亚洲最大免费视频网| www.91亚洲| 亚洲黄色免费观看| 老妇激情毛片免费| 亚洲高清在线mv|