<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
    主站蜘蛛池模板: 四虎亚洲国产成人久久精品| 亚洲一区二区三区在线网站| 爱情岛论坛免费视频| 成人毛片18岁女人毛片免费看| 免费夜色污私人影院在线观看| 亚洲国产另类久久久精品| 韩国免费A级毛片久久| 亚洲永久无码3D动漫一区| 亚洲欧美日韩国产成人| 无码人妻久久一区二区三区免费丨| 亚洲成a人片7777| www.黄色免费网站| 亚洲人成自拍网站在线观看| 日本免费观看网站| 特级毛片aaaa免费观看| 国产亚洲精品免费视频播放| 精品无码国产污污污免费网站国产| 亚洲色欲久久久综合网| 91精品国产免费久久国语蜜臀| 亚洲色欲一区二区三区在线观看| 日韩电影免费观看| 亚洲中文字幕一二三四区苍井空| 在线看片人成视频免费无遮挡| 蜜芽亚洲av无码一区二区三区| 亚洲熟女乱综合一区二区| 久久成人免费播放网站| 亚洲夂夂婷婷色拍WW47| 亚洲国产高清在线一区二区三区| 久久精品免费网站网| 亚洲欧洲日产韩国在线| 国产jizzjizz视频全部免费| 亚洲欧洲无码AV不卡在线| 亚洲天堂中文字幕在线| 一级毛片免费不卡在线| 亚洲AV无码国产在丝袜线观看| 免费成人福利视频| 黄色a三级免费看| 久久精品国产亚洲AV电影| 久久久久久一品道精品免费看| 亚洲女人影院想要爱| 免费人成在线观看播放国产|