SQL 統一編號(aka統編)的檢查方法

統一編號檢查方法

(擴增統一編號-預計112年4月1日啟用)

統編這是第三次改版重寫，

也做了好幾次的修改。 檢查方法的規則，這邊就先省略了。

重點是「擴增統一編號」這一版， 統編第七碼，如果是「7」，

最後可以用「1」或「0」。

然後，如果

[加總] + [第七碼1] 的尾數等於 0 或 5，

或是

[加總] + [第七碼2] 的尾數等於 0 或 5， 表示統編的編碼是符合規則的。

SELECT [統編]

      ,'第七碼'=Substring([統編],7,1) /* 統編第七碼等於「7」是特例，有兩種判斷方式。 */

       

      ,'加總'=

       /* 加總第 1、3、5、8 碼 乘以 邏輯乘數「1」乘積。 */

       CONVERT(INT,Substring([統編],1,1))*1 +

       CONVERT(INT,Substring([統編],3,1))*1 +

       CONVERT(INT,Substring([統編],5,1))*1 +

       CONVERT(INT,Substring([統編],8,1))*1 +

       /* 加總第 2、4、6 碼 乘以 邏輯乘數「2」乘積直寫並上下相加之合。 */

       CASE Substring([統編],2,1) WHEN 0 THEN 0

       ELSE CONVERT(INT,Substring('246813579', CONVERT(INT,Substring([統編],2,1)) ,1)) END +

       CASE Substring([統編],4,1) WHEN 0 THEN 0

       ELSE CONVERT(INT,Substring('246813579', CONVERT(INT,Substring([統編],4,1)) ,1)) END +

       CASE Substring([統編],6,1) WHEN 0 THEN 0

       ELSE CONVERT(INT,Substring('246813579', CONVERT(INT,Substring([統編],6,1)) ,1)) END

      ,'第七碼1'=CONVERT(INT,

       CASE Substring([統編],7,1)

       WHEN 1 THEN 4 /* 1 * 4 = 4 */

       WHEN 2 THEN 8 /* 2 * 4 = 8 */

       WHEN 3 THEN 3 /* 3 * 4 = 12 > 1 + 2 = 3 */

       WHEN 4 THEN 7 /* 4 * 4 = 16 > 1 + 6 = 7 */

       WHEN 5 THEN 2 /* 5 * 4 = 20 > 2 + 0 = 2 */

       WHEN 6 THEN 6 /* 6 * 4 = 24 > 2 + 4 = 6 */

       WHEN 7 THEN 0 /* 7 * 4 = 28 > 2 + 8 = 10 > 用 1 或 0 。 這邊用 0 */

       WHEN 8 THEN 5 /* 8 * 4 = 32 > 3 + 2 = 5 */

       WHEN 9 THEN 9 /* 9 * 4 = 36 > 3 + 6 = 9 */

       WHEN 0 THEN 0 /* 0 * 4 = 0 */ END )

      ,'第七碼2'= /* 說明：         第七碼 * 4 的乘積，再垂直相加。對應1~9  */

       CASE Substring([統編],7,1) /* │  利用 Substring 取指定值的規則，當第七碼是 2 時，就取得 8。 */

       WHEN 0 THEN 0 /*             ↓      ↓ */

       ELSE CONVERT(INT,Substring('483726159', CONVERT(INT,Substring([統編],7,1)) ,1)) END

  FROM [申報檔]

然後上面這邊我是捨棄部分的計算式， 反正乘積結果是固定的， 只要用 Substring 函數的規則特性， 將數字對應到乘積直寫上下相加的結果就好了。

CASE 函數

/* case 函數*/

--對欄位作CASE的判斷。

SELECT s.[std_name] + CASE p.[addr] when '新竹市' then '市民' else '訪客' end

      ,p.[addr]

  from [personal] p,[score] s

 where p.[std_num]=s.[std_num]

--對欄位下條件作CASE的判斷。

SELECT s.[std_name] + CASE when p.[addr] like '新竹%' then '在地人' else '觀光客' end

      ,p.[addr]

  from [personal] p,[score] s

 where p.[std_num]=s.[std_num]

SELECT [std_num]

      ,[std_name]

      ,[CO]

      ,[DB]

      ,[SP]

      ,[TD]

      ,[judgment]

      ,總成績=co+db+sp+td

      ,本次考試表現=case 

                    when co+db+sp+td>=360 then '非常好!'

                    when co+db+sp+td>=320 then '再加油!'

                    else '太混了!' end

  FROM [CSIE98].[dbo].[score]

--對排序作CASE的判斷。

SELECT [員工編號]

      ,[姓名]

      ,[職稱]

      ,[性別]

      ,[主管]

      ,[出生日期]

      ,[任用日期]

      ,[區域號碼]

      ,[地址]

      ,[分機號碼]

  FROM [CH11範例資料庫].[dbo].[員工]

 ORDER BY CASE [性別] when '男' then [出生日期] else [任用日期] end

話說，

CASE 函數可以使用在排序條件上，這點有令我意外。

因為我還真不知道可以這樣子用呢！

MERGE 命令語法 ( 限 SQL 2008 以上使用 )

試了好幾次，寫好的 MERGE 命令語法完~~~~全不能用。

去爬文後，看到的答案，大多都是說「不支援SQL 2005」。
 

既然 MERGE 這個命令語法，不支援SQL 2005。

就記錄一下，以備日後可以參考。

/* MERGE 命令語法 */

 MERGE M目標_產品資料 AS T

 USING M來源_產品資料 AS S

    ON T.產品編號= S.產品編號

  /* 當目標資料表與來源資料表條件吻合時，執行下面動作。*/

  WHEN MATCHED

  THEN UPDATE

          SET T.產品名稱=S.產品名稱, T.單價=S.單價

  /* 當目標資料表(T)中不存，來源資料表(S)中有的產品編號時，執行下面動作。*/

  WHEN NOT MATCHED BY TARGET

  THEN INSERT (產品編號, 產品名稱, 單價,)

       VALUES (S.產品編號, S.產品名稱, S.單價, 0)

  /* 當目標資料表(T)中存在，來源資料表(S)不存在此產品編號時，執行下面動作。*/

  WHEN NOT MATCHED BY SOURCE

  THEN DELETE

; /* 一定要加分號做結尾，否則會出錯。 */

雖然還沒機會可以使用，但MERGE看起來就是可以一次比對完兩個資料表中的差異，並且將來源資料表(SOURCE)中的結果，異動到目標資料表(TARGET)，完成UPDATE、INSERT、DELETE等動作。

合併複數資料表的方式

/* 合併資料表*/

合併兩張資料表的方式，大概有下面三種。

--(1)用逗點分隔

select * from score,personal

--(2)使用INNER JOIN

select * from score INNER JOIN personal

--(3)省略INNER，只使用JOIN

select * from score JOIN personal

下面三個語法，有的只有 JOIN 條件，有的只有 WHERE 條件，但結果都是一樣。

--(1)

--下面的語法，把INNER JOIN 中 ON 的條件挪到 where 裡了。

select *

  from score S,personal P

 where S.std_num=P.std_num and p.addr like '_竹_'

--(2)

select *

  from score S join personal P

    on S.std_num=P.std_num

 where p.addr like '_竹_'

--(3)
--下面語法沒有使用 where 是可以的，但僅適合簡易的資料查詢。

--「p.addr like '_竹_'」合併到 JOIN 的條件裡了。

select *

  from score S join personal P

    on S.std_num=P.std_num and p.addr like '_竹_'

用逗點來做 INNER JOIN，對我來說，算是蠻新奇的作法。
我以前都不知道可以這樣使用...

使用模糊查詢

/* 模糊查詢*/

在資料表中，取出符合條件的資料的方法很多。

可以在 Where 中使用 Like、in，或是使用 LEFT、RIGHT、SUBSTRING等等的函式。

--例如找出住在新竹縣市的人。

select * from [personal] where left([addr],2)='新竹'

select * from [personal] where substring([addr],1,2)='新竹'

select * from [personal] where [addr] like '新竹%'

select * from [personal] where [addr] in ('新竹市','新竹縣')

--底線'_'是指任何字元，雖然可以模糊查詢，但會受到字元長度限制。

--一個底線代表一個字元。

select * from [personal] where [addr] like '新竹_'

select * from [personal] where [addr] like '新__'

--使用中括弧也是模糊查詢的一種。

select * from [personal] where [addr] like '新竹[縣市]'

--另外老師有教[^北]

--這個「^」到底是什麼意思？下次上課要問清楚。

select * from [personal] where [addr] like '新[^北][縣市]'

擷取欄位中的部分字串

/* 截取字串*/

--LEFT( character_expression , integer_expression )

--LEFT( [字串], [數字(要擷取的長度)] )

SELECT LEFT(std_name,1) FROM SCORE

--RIGHT( character_expression , integer_expression )

--RIGHT( [字串], [數字(要擷取的長度)] )

SELECT RIGHT(std_name,1) FROM SCORE

--SUBSTRING( expression ,start , length )

--SUBSTRING( [字串], [數字(取值的起始位置)] , [數字(要擷取的長度)] )

select substring(std_name,1,1) 姓氏from score

select substring(std_name,2,10) 名字from score

substring在VB用好久了，我竟然不知道在SQL裡面也有一樣的函式可以用。

我太孤陋寡聞了...。