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 BY 以 S_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 |
---|---|---|---|
1 | Los Angeles | 900 | 2020/03/05 |
2 | San Diego | 1500 | 2020/03/07 |
3 | Los Angeles | 300 | 2020/03/12 |
4 | Boston | 1200 | 2020/03/08 |
5 | Boston | 1200 | 2020/03/08 |
6 | New Jersey | 700 | 2020/03/18 |
7 | Taiwan | 850 | 2020/09/08 |
8 | Boston | 200 | 2020/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_Name 及 S_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 |
---|---|
5 | Boston |
8 | Boston |
1 | Los Angeles |
3 | Los Angeles |
6 | New Jersey |
2 | San Diego |
7 | Taiwan |
由列出的記錄中得到索引鍵 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 相反的列出不包含在這範圍裡的。只有索引鍵 4 的 Boston 欄位值重覆的記錄。
S_Index | S_Name |
---|---|
4 | Boston |
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 |
---|---|
8 | Boston |
3 | Los Angeles |
6 | New Jersey |
2 | San Diego |
7 | Taiwan |
列出的記錄中得到索引鍵 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_Index | S_Name |
---|---|
1 | Los Angeles |
4 | Boston |
5 | Boston |
刪除重複記錄
同樣使用 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_Name | S_Qty rs(1) |
---|---|
Boston | 2600 |
Los Angeles | 1200 |
New Jersey | 700 |
San Diego | 1500 |
Taiwan | 850 |
加入 WHERE 條件,統計庫存量小於 1000 的倉庫記錄。
SELECT S_Name, SUM(S_Qty)
FROM Store_Information
WHERE S_Qty < 1000 GROUP BY S_Name
S_Name | S_Index rs(0) |
---|---|
Boston | 200 |
Los Angeles | 1200 |
New Jersey | 700 |
Taiwan | 850 |
統計倉庫名稱相同的庫存量,且條件庫存量需大於 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_Name | S_Qty rs(1) |
---|---|
Boston | 2600 |
Los Angeles | 1200 |
San Diego | 1500 |
統計倉庫名稱相同的庫存量,且條件庫存量需小於 1000 的倉庫記錄。
SELECT S_Name, SUM(S_Qty)
FROM Store_Information
GROUP BY S_Name HAVING (SUM(S_Qty) < 1000)
S_Name | S_Qty rs(1) |
---|---|
New Jersey | 700 |
Taiwan | 850 |
AVG 平均值
統計倉庫名稱相同的庫存量平均值 Average、Aggregate。
SELECT S_Name, AVG(S_Qty)
FROM Store_Information GROUP BY S_Name
S_Name | S_Qty rs(1) |
---|---|
Boston | 866.666666666667 |
Los Angeles | 600 |
New Jersey | 700 |
San Diego | 1500 |
Taiwan | 850 |
例如 Boston = 1200 + 1200 + 200 = 2600 / 3 = 866.666666666667
SQL BETWEEN 包含在二個值之間的記錄資料
列出庫存量在於 500 與 1000 之間的記錄。
SELECT * FROM Store_Information
WHERE S_Qty BETWEEN 500 AND 1000
S_Index | S_Name | S_Qty rs(1) | S_Date |
---|---|---|---|
1 | Los Angeles | 900 | 2020/03/05 |
6 | New Jersey | 700 | 2020/03/18 |
7 | Taiwan | 850 | 2020/09/08 |
列出倉庫名稱在於 'M' 與 'Z' 之間的記錄。
SELECT * FROM Store_Information
WHERE S_Qty BETWEEN 'M' AND 'Z'
S_Index | S_Name | S_Qty rs(1) | S_Date |
---|---|---|---|
2 | San Diego | 1500 | 2020/03/07 |
6 | New Jersey | 700 | 2020/03/18 |
7 | Taiwan | 850 | 2020/09/08 |
NOT BWTWEEN
BETWEEN 相反的 NOT BWTWEEN 就是不包含在這範圍裡的的資料。
SELECT * FROM Store_Information
WHERE S_Qty NOT BETWEEN 'M' AND 'Z'
S_Index | S_Name | S_Qty rs(1) | S_Date |
---|---|---|---|
8 | Boston | 200 | 2020/03/10 |
1 | Los Angeles | 900 | 2020/03/05 |
3 | Los Angeles | 300 | 2020/03/12 |
4 | Boston | 1200 | 2020/03/08 |
5 | Boston | 1200 | 2020/03/08 |