实用的一条SQL语句…(持续更新…)

[1] connect by 的应用
-- 列出某范围的天数 (仅10g)
SELECT trunc(SYSDATE, 'MM')+ROWNUM-1 dt
  FROM dual
CONNECT BY ROWNUM <= last_day(SYSDATE) - trunc(SYSDATE, 'MM') + 1

-- 列出左右五天
SELECT trunc(SYSDATE) + (ROWNUM - 6) dt FROM dual CONNECT BY ROWNUM <= 11

[2] 合并记录(merge = insert + update)
将B表的数据根据条件合并到A表，如果对应的ID存在就Update,否则就Insert新的

TA表:  FID      FBALANCE
          1          1500
          2          1200
          3          2600

TB表:  FID      FBALANCE
          2          1500
          4          1200

合并语句:
MERGE INTO ta a USING tb b ON(a.fid = b.fid)
  WHEN MATCHED THEN
    UPDATE SET fBalance = fBalance + b.fBalance
  WHEN NOT MATCHED THEN
    INSERT (fid, fbalance) VALUES(b.fid, b.fbalance);

TA的结果为: FID      FBALANCE
                 1          1500
                 2          3000
                 3          2600
                 4          1200

[3] 找出重复的记录
方法一: SELECT *
            FROM tbl_name
          WHERE ROWID NOT IN(SELECT MIN(ROWID) FROM tbl_name GROUP BY fid)

方法二: SELECT *
            FROM (SELECT a.*,
                                 row_number() over(PARTITION BY fid ORDER BY createTime DESC) rn
                        FROM tbl_name a)
          WHERE rn > 1

[4] 求积
原理: a * b * c = power(10, log(10, a) + log(10, b) + log(10, c)
格式: SELECT power(10,SUM(LOG(10,数值列))) FROM 表名;
例句: SELECT power(10,SUM(LOG(10,ROWNUM))) item FROM dual CONNECT BY ROWNUM <= 5;
       ITEM
       ----------
       120

[5] 行列转换
原理: 分组加上decode函数来区分列名,并显示对应的数据
例子: 姓名    季度      收入
       张三    1    10020
       张三    4    18020
       李四    1    20020
       王五    1    20020
       刘六    1    17020
       刘六    3    6020
       刘六    4    24020

SELECT fname,
       SUM(decode(fseason, 1, fBalance, 0)) season1,
       SUM(decode(fseason, 2, fBalance, 0)) season2,
       SUM(decode(fseason, 3, fBalance, 0)) season3,
       SUM(decode(fseason, 4, fBalance, 0)) season4
  FROM tfin_salary
 GROUP BY fname
-------------------------------------------------------
李四    20020    0    0    0
刘六    17020    0    6020    24020
王五    20020    0    0    0
张三    10020    0    0    18020

[6] 查找含'_'和'%'字符的数据
FSTRING
---------------
abc%$_%
a%bc
%abc
abc_
a_bc

SELECT * FROM tbl_string WHERE fString LIKE '%_%' ESCAPE '';

FSTRING
---------------
abc%$_%
abc_
a_bc

SELECT * FROM tbl_string WHERE fString LIKE '%%%' ESCAPE '';

FSTRING
---------------
abc%$_%
a%bc
%abc