另類的LIKE寫法

以 Access 為例:
找出欄位中符合部分字串內容的資料
where "abcdefg" like "*"+資料表.欄位名+"*"
欄位中有, abc, cde, efg, 等資料都會顯示出來.

自寫 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 

ACCESS 排名取值

ACCESS 沒有ROW_NUMBER()的功能,因此若要排名次則改用如下的方式,

CASE 1: 在相同購買物品項目中,找出最後2次購買的資料:

SELECT X.購買物品, X.第幾次, X.價格, count(Y.購買物品) AS Rank, (X.購買物品 &  '-' & X.第幾次 ) as ID
FROM DataTable AS X, DataTable AS Y
WHERE X.購買物品 = Y.購買物品 and
           (X.第幾次 <  Y.第幾次 or (X.購買物品 = Y.購買物品 and X.第幾次= Y.第幾次  ))
GROUP BY X.購買物品, X.第幾次, X.價格
HAVING count(Y.購買物品) <= 2;

CASE 2: 在相同購買物品項目中,僅保留最後2次購買的資料:

Delete from DataTable where (購買物品 &  '-' & 第幾次 ) not in 
(
SELECT (X.購買物品 &  '-' & X.第幾次 ) as ID
FROM DataTable AS X, DataTable AS Y
WHERE X.購買物品 = Y.購買物品 and
      (X.第幾次 <  Y.第幾次 or (X.購買物品 = Y.購買物品 and X.第幾次= Y.第幾次))
GROUP BY X.購買物品, X.第幾次, X.價格
HAVING count(Y.購買物品) <= 2
)
PS:若是DataTable有主索引,則更方便操作.

ACCESS 多重 JOIN 疑問

ACCESS 不能同時使用兩個LEFT JOIN 嗎? 疑 ?
怎麼試都不成功...
SELECT CC.編號,CC.品名,AA.表單A數量和,BB.表單B數量和,(AA.表單A數量和-BB.表單B數量和) AS 兩表數量差異
FROM
(SELECT 表單A.編號,表單A.品名 FROM 表單A UNION SELECT 表單B.編號,表單B.品名 FROM 表單B ) AS CC
LEFT JOIN (SELECT 表單A.編號,表單A.品名,SUM(表單A.數量) AS 表單A數量和 FROM 表單A GROUP BY 表單A.編號,表單A.品名) AS AA ON CC.編號 = AA.編號
LEFT JOIN (SELECT 表單B.編號,表單B.品名,SUM(表單B.數量) AS 表單B數量和 FROM 表單B GROUP BY 表單B.編號,表單B.品名) AS BB ON CC.編號 = BB.編號
最後改變方法...把資料都先UNION ALL 起來, 雖然WORKing, 但,這種邏輯是否是當呢? 記憶體...效能...

SELECT 編號,
       品名, 
       SUM(IIF(ST='A',數量,0)) AS 表單A數量,
       SUM(IIF(ST='A',0,數量)) AS 表單B數量,
       abs(SUM(IIF(ST='A',數量,數量*-1))) AS 兩表數量差異
FROM  (SELECT 'A' AS ST, 編號, 品名, 數量 FROM 表單A  
       UNION ALL
       SELECT 'B' AS ST, 編號, 品名, 數量 FROM 表單B
      ) AS CC
GROUP BY 編號, 品名 

PS: 其他SQL 可以用case when 替換 iif 達到類似的目的.

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))

Access 格式化日期函式

SELECT TEST.名字,
       TEST.出生日期,
       Year(Date()) & Format(TEST.出生日期,"/MM/DD") as 今年生日
FROM TEST

Recordset.Filter =
"今年生日 >= '" & Format(Date(),"YYYY/MM/DD") &
"' AND 今年生日 <= '"& Format(Date()+7,"YYYY/MM/DD") & "'"

SELECT TEST.名字,   TEST.出生日期  FROM TEST 
WHERE ( Year(Date()) & Format(TEST.出生日期,"/MM/DD"))
          >= Format(Date(),"YYYY/MM/DD")
  and ( Year(Date()) & Format(TEST.出生日期,"/MM/DD"))
        <= Format(Date()+7,"YYYY/MM/DD");  

Access [保留字] 的用法

Ａccess 欄位名稱使用保留字,導致 UPDATE 陳述式的語法錯誤。

如果已經使用保留字，您可以在每個保留字出現之處使用括號 ([ ]) 括起來，
例如: [password] , 以避免產生錯誤訊息。 但是最好的解決方案是把名稱變更為非保留字。

http://office.microsoft.com/zh-tw/access/HA100306431028.aspx