SQL取得第一筆資料

Oracle : where rownum=1
MsSQL: select TOP 1
MySQL: Limit 1

自寫 rownum 的方法

Oracle SQL有個 rownum 的內建功能, 可以將select 的結果自動編號.

例如:找出以下表格欄位TEST1.L為偶數的資料列, 並給予編號如下,
　
select rownum, L, P, Q from test1 where mod(L,2)=0;
　
   ROWNUM         L P         Q
--------- --------- - ---------
        1         2 B         2
        2         4 C         3
        3         6 A         3
        4         8 G         3

其實也可以用以下SQL語法,達到相同的目的,
　
select count(y.L) my_rownum, x.L, x.P, x.Q
  from test1 x, test1 y
  where x.L >= y.L
    and mod(x.L,2)=0 --->
    and mod(y.L,2)=0 ---> 注意:x,y條件皆相同
  group by x.L, x.P, x.Q;
　
MY_ROWNUM         L P         Q
--------- --------- - ---------
        1         2 B         2
        2         4 C         3
        3         6 A         3
        4         8 G         3
　
PS:這種自身join的方法,也或許可以用在其他資料庫或不支援rownum的情況.
　
再加一個範例: 相同x.cus_id, 按照 x.risk,x.bra_id,x.pr_rank_name,x.pro_name 排名
　
select count(y.cus_id) as num,
x.cus_id,x.risk,x.bra_id,x.pr_rank_name,x.pro_name 
from aaa x, aaa y
where x.cus_id = y.cus_id
  and ((x.risk > y.risk) or
       ((x.risk = y.risk) and (x.bra_id > y.bra_id)) or
       ((x.risk = y.risk) and (x.bra_id = y.bra_id) and (x.pr_rank_name > y.pr_rank_name)) or
       ((x.risk = y.risk) and (x.bra_id = y.bra_id) and (x.pr_rank_name = y.pr_rank_name) and (x.pro_name > y.pro_name )) or
       ((x.risk = y.risk) and (x.bra_id = y.bra_id) and (x.pr_rank_name = y.pr_rank_name) and (x.pro_name = y.pro_name ))
      )
group by x.cus_id,x.risk,x.bra_id,x.pr_rank_name,x.pro_name 

SQL 流水號字串補零

輸出 "YYYYMMDD"
1. CONVERT(varchar(8), GETDATE(), 112)

輸出 "0123"
1. REPLICATE('0', 4 - LEN(CONVERT(char(4), 123))) + CONVERT(char(4), 123)
2. RIGHT('0000' + CONVERT(varchar(4), 123), 4)
3. RIGHT('0000' + CAST(123 as varchar), 4)

輸出結果:YYYYMMDD流水號 =>
1. CONVERT(varchar(8), GETDATE(), 112) + RIGHT('0000'+CAST(續號 as varchar),4)

PS:CAST 像是 CONVERT 的陽春版了.

Row_number 的使用

利用分析函數,求得排名, 再利用排名,來控制資料輸出格式.(以下為MsSQL語法,Oracle 也有類似功能)

SELECT (CASE WHEN rank2 = 1 THEN aaa ELSE '' END) AS BreakA, bbb 
       --> 相同的 aaa 只出現一次,像作報表一樣
FROM 
   (SELECT c.aaa, c.bbb, 
       row_number() OVER
       (partition BY c.aaa, c.bbb ORDER BY c.aaa, c.bbb) AS rank1,
       row_number() OVER 
       (partition BY c.aaa ORDER BY c.aaa, c.bbb) AS rank2
    FROM dbo.ItemA AS a LEFT OUTER JOIN dbo.ItemC AS c
    ON a.ItemID = c.ItemID) AS g
WHERE rank1 = 1 ---> 類似 distinct 的效果
ORDER BY aaa, bbb

註:
row_number():逐列排名,重複資料,不允許相同排名,仍有第1,2名之分.
rank():允許有2個第1名,此時就沒有第2名,直接跳第3名.
dense_rank():允許有2個第1名,第3個則為第2名,即名次不跳號.
ACCESS 不支援,其他資料庫尚未測過.
ACCESS 可以參考自身JOIN的方法達到類似的目的.

SQL Update 特殊寫法

利用 Case When 選擇性的update 欄位內容 (Oracle 8.1.7, MsSQL, MySQL 適用)
Oracle 判斷的較不嚴謹, null 與 空字串看來相同...

表格T1:
SQL> select * from t1;

  USER_ID CLASS
--------- --------------------
        1 A
        2
        3 C

語法:
SQL> update t1 set class = (case when class is null then 'NullValue' else class end);

3 rows updated.

結果:
SQL> select * from t1;

  USER_ID CLASS
--------- --------------------
        1 A
        2 NullValue
        3 C

MsSQL 與 MySQL 的用法,有分空字串與NULL

update t1 set class = (case when class='' then 'EmptyValue' else class end);
update t1 set class = (case when class is null then 'NullValue' else class end);


於Oracle 還有decode 的用法,可以達到雷同的效果,
update t1 set class = decode(class,null,'NULLVALUE',class);
於ACCESS 還有iif 的用法,可以達到雷同的效果,

Private Sub do_sql_Click()
On Error GoTo Err_do_sql_Click


Dim SQL As String
    
    'OK: 但沒有必要多加判斷
    SQL = "UPDATE [員工資料] " & _
          "SET [部門代號] = iif([部門代號]='' or [部門代號] is null ,'NullValue',[部門代號]) " & _
          "WHERE [工號] = '908' "
          
    'NG: 以空字串判斷讀不到新增record的 null也讀不到修改後的空白欄位
    SQL = "UPDATE [員工資料] " & _
          "SET [部門代號] = iif([部門代號]='','空字串',[部門代號]) " & _
          "WHERE [工號] = '908' "
          
    'OK! 測試發現, ACCESS 不認空字串'' 型態,用null即可包含空字串.
    SQL = "UPDATE [員工資料] " & _
          "SET [部門代號] = iif([部門代號] is null,'nullvalue',[部門代號]) " & _
          "WHERE [工號] = '907' "
    
    DoCmd.RunSQL SQL
    MsgBox (SQL)
    

Exit_do_sql_Click:
    Exit Sub

Err_do_sql_Click:
    MsgBox Err.Description
    Resume Exit_do_sql_Click
    
End Sub

補充:
於 MsSQL 必須留意, 空字串''與 NULL 是不同的!
空字串於 CASE WHEN 可以用 CLASS = '' 表示比較條件式,
NULL 於 CASE WHEN 應該用 CLASS IS NULL 表示條件式, 或利用 ISNULL(CLASS,'NULLVALUE')轉換之.

PS: 其他資料庫尚未實測.

SQL Group By 直轉橫

Oracle SQL, MySQL, 通用的語法, 一般的group by 統計方式,為直列式由上而下逐筆顯示,
以下方式, 通常用於將特定已知的"資料",轉向作為[欄位]後,進行分析統計.
簡單的說, 就是原來直放的資料，變成橫放.
範例:
按各個userid, 橫向呈現於各個 CLASS_A,B,C 統計分析相關資料出現的次數.

SELECT user_id, sum(CLASS_A) AS CLASS_A,
                sum(CLASS_B) AS CLASS_B,
                sum(CLASS_C) AS CLASS_C
FROM ( select user_id,
              (CASE WHEN CLASS = 'a' THEN 1 ELSE '' END) AS CLASS_A,
              (CASE WHEN CLASS = 'b' THEN 1 ELSE '' END) AS CLASS_B,
              (CASE WHEN CLASS = 'c' THEN 1 ELSE '' END) AS CLASS_C
       from T1
     )
GROUP BY USER_ID
ORDER BY USER_ID;

簡化的作法,結果一樣....

SELECT user_id,
       sum(CASE WHEN CLASS = 'a' THEN 1 ELSE '' END) AS CLASS_A,
       sum(CASE WHEN CLASS = 'b' THEN 1 ELSE '' END) AS CLASS_B,
       sum(CASE WHEN CLASS = 'c' THEN 1 ELSE '' END) AS CLASS_C
FROM T1
GROUP BY USER_ID
ORDER BY USER_ID;

PS:若是ACCESS 資料庫, case when 換成iff 的語法即可,
如: SUM(IIF(CLASS = 'a', 1, 0))

SQL Case When 的使用

Oracle SQL, MySQL, MsSQL 通用的語法, 可以選擇性的加工輸出欄位值:
範例 1:

select user_id,
　　　(CASE WHEN CLASS = 'a' THEN 1 ELSE '' END) AS CLASS_A,
　　　(CASE WHEN CLASS = 'b' THEN 1 ELSE '' END) AS CLASS_B,
　　　(CASE WHEN CLASS = 'c' THEN 1 ELSE '' END) AS CLASS_C
from T1;

範例 2:

select id, (CASE WHEN 數據 = 0 THEN '沒有數據' ELSE 數據 END) AS "數據值"
from T2;

PS:還可以用於 update set=(case when...), where xxx=(case when...) 敘述中