SQL 找出欄位值重覆的記錄及刪除重複記錄

使用 SQL 的 GROUP BY 找出資料表中欄位值重覆的記錄,將資料表按照分組,然後計算每個分組的記錄數。

set myConnection = Server.CreateObject("ADODB.Connection")
Provider="Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("db/Northwind.mdb")

Store_Information 資料表內容

欄位排列 ORDER BYS_Index 的順序產生資料內容。

SELECT * FROM Store_Information ORDER BY S_Index
Do while NOT rs.EOF
  Response.Write rs("S_Index") & rs("S_Name") & rs("S_Qty") & rs("S_Date")
rs.MoveNext
loop
索引鍵S_Index倉庫名稱S_Name庫存量S_Qty單據日期S_Date
1Los Angeles9002020/03/05
2San Diego15002020/03/07
3Los Angeles3002020/03/12
4Boston12002020/03/08
5Boston12002020/03/08
6New Jersey7002020/03/18
7Taiwan8502020/09/08
8Boston2002020/03/10

DISTINCT 找出所有不同的資料值 (無重複記錄)

在 SELECT 查詢語句中使用 DISTINCT 關鍵字過濾重複出現的記錄值,欄位內找出所有不同的記錄。

SELECT DISTINCT Store_name FROM Store_Information
S_Name
Boston
Los Angeles
New Jersey
San Diego
Taiwan


找出欄位值重覆的記錄

GROUP BY 計算出相符的資料值

某情況下 ACCESS 資料表的部份欄位可能會有相同的值,匯總數據之聚合函數與分組 GROUP BY 敘述句搭配聚合函數 Aggregate Function 使用,將查詢結果中特定欄位值相同的資料分為若干個群組,而每一個群組都會傳回一個資料列。若沒有使用 GROUP BY 聚合函數針對一個 SELECT 查詢,只會返回一個彙總值。

1 找出倉庫名稱及日期均相同的記錄(兩個欄位均相同)

GROUP BY 計算出相同的 S_NameS_Date 資料記錄,條件為倉庫名稱及日期均相同。

並且由變數 SelectIndex 保留列出結果的索引鍵。

SELECT MAX(S_Index), S_Name, S_Date
   FROM Store_Information
   GROUP BY S_Name, S_Date HAVING (COUNT(*) > 0)
Dim SelectIndex
Do while NOT rs.EOF
  Response.Write rs(0) & rs("S_Name")
  SelectIndex = SelectIndex & rs(0) & ","
rs.MoveNext
loop
S_Index rs(0)S_Name
5Boston
8Boston
1Los Angeles
3Los Angeles
6New Jersey
2San Diego
7Taiwan

由列出的記錄中得到索引鍵 5, 8, 1, 3, 6, 2, 7,

IN 搭配 WHERE 子句可以用來限定必需符合某些欄位值為條件,來搜尋資料表中的特定(已知)資料記錄。

相反的 NOT IN 就是不包含在這範圍裡的的資料記錄。

SELECT * FROM Store_Information
   WHERE S_Index NOT IN ( & SelectIndex & ) ORDER BY S_Index
Do while NOT rs.EOF
  Response.Write rs("S_Index") & rs("S_Name")
rs.MoveNext
loop

使用 NOT IN 相反的列出不包含在這範圍裡的。只有索引鍵 4Boston 欄位值重覆的記錄。

S_IndexS_Name
4Boston

2 找出僅倉庫名稱相同的記錄

GROUP BY 計算出相同的資料值 S_Name 為倉庫名稱相同。

SELECT MAX(S_Index), S_Name
   FROM Store_Information
   GROUP BY S_Name HAVING (COUNT(*) > 0)
Dim SelectIndex
Do while NOT rs.EOF
  Response.Write rs(0) & rs("S_Name")
  SelectIndex = SelectIndex & rs(0) & ","
rs.MoveNext
loop
S_Index rs(0)S_Name
8Boston
3Los Angeles
6New Jersey
2San Diego
7Taiwan

列出的記錄中得到索引鍵 8, 3, 6, 2, 7,

SELECT * FROM Store_Information
   WHERE S_Index NOT IN ( & SelectIndex & ) ORDER BY S_Index
Do while NOT rs.EOF
  Response.Write rs("S_Index") & rs("S_Name")
rs.MoveNext
loop

使用 NOT IN 相反的列出結果,不包含在這範圍裡的有三筆,倉庫名稱相同欄位值重覆的記錄。

S_IndexS_Name
1Los Angeles
4Boston
5Boston

刪除重複記錄

同樣使用 NOT IN 刪除結果。

DELETE FROM Store_Information
   WHERE S_Index NOT IN ( & SelectIndex & )



Aggregate Function 聚合函數

Aggregate Function 聚合函數,指的也就是 AVG()COUNT()MAX()MIN()SUM() 等這些內建函數。

SUM

統計倉庫名稱相同的庫存量

SELECT S_Name, SUM(S_Qty)
   FROM Store_Information GROUP BY S_Name
S_NameS_Qty rs(1)
Boston2600
Los Angeles1200
New Jersey700
San Diego1500
Taiwan850


加入 WHERE 條件,統計庫存量小於 1000 的倉庫記錄。

SELECT S_Name, SUM(S_Qty)
   FROM Store_Information
   WHERE S_Qty < 1000 GROUP BY S_Name
S_NameS_Index rs(0)
Boston200
Los Angeles1200
New Jersey700
Taiwan850

統計倉庫名稱相同的庫存量,且條件庫存量需大於 1000 的倉庫記錄。


HAVING 子句是用來取代 WHERE 搭配聚合函數 Aggregate Function 進行條件查詢,因為 WHERE 不能與聚合函數一起使用。
SQL 提供 HAVING 的指令,篩選一或多個欄位名稱,可以用這個指令來條件篩選。HAVING 子句通常是在 SQL 句子的最後。

SELECT S_Name, SUM(S_Qty)
   FROM Store_Information
   GROUP BY S_Name HAVING (SUM(S_Qty) > 1000)
S_NameS_Qty rs(1)
Boston2600
Los Angeles1200
San Diego1500


統計倉庫名稱相同的庫存量,且條件庫存量需小於 1000 的倉庫記錄。

SELECT S_Name, SUM(S_Qty)
   FROM Store_Information
   GROUP BY S_Name HAVING (SUM(S_Qty) < 1000)
S_NameS_Qty rs(1)
New Jersey700
Taiwan850

AVG 平均值

統計倉庫名稱相同的庫存量平均值 Average、Aggregate。

SELECT S_Name, AVG(S_Qty)
   FROM Store_Information GROUP BY S_Name
S_NameS_Qty rs(1)
Boston866.666666666667
Los Angeles600
New Jersey700
San Diego1500
Taiwan850

例如 Boston = 1200 + 1200 + 200 = 2600 / 3 = 866.666666666667

SQL BETWEEN 包含在二個值之間的記錄資料

列出庫存量在於 5001000 之間的記錄。

SELECT * FROM Store_Information
   WHERE S_Qty BETWEEN 500 AND 1000
S_IndexS_NameS_Qty rs(1)S_Date
1Los Angeles9002020/03/05
6New Jersey7002020/03/18
7Taiwan8502020/09/08


列出倉庫名稱在於 'M''Z' 之間的記錄。

SELECT * FROM Store_Information
   WHERE S_Qty BETWEEN 'M' AND 'Z'
S_IndexS_NameS_Qty rs(1)S_Date
2San Diego15002020/03/07
6New Jersey7002020/03/18
7Taiwan8502020/09/08

NOT BWTWEEN

BETWEEN 相反的 NOT BWTWEEN 就是不包含在這範圍裡的的資料。

SELECT * FROM Store_Information
   WHERE S_Qty NOT BETWEEN 'M' AND 'Z'
S_IndexS_NameS_Qty rs(1)S_Date
8Boston2002020/03/10
1Los Angeles9002020/03/05
3Los Angeles3002020/03/12
4Boston12002020/03/08
5Boston12002020/03/08

匯整資料表中月份範圍的記錄