文章目錄
- 前言
- ⛳️ 1.distinct
- ⛳️ 2.group by
- ⛳️ 3.row_number 視窗函數
- ⛳️ 4.删除重複資料
- ⛳️ 5.如何成為SQL高手
前言
在使用SQL提數的時候，常會遇到表内有重複值的時候，就需要做去重，本文歸類了常用方法。
⛳️ 1.distinct
題目：現在營運需要檢視使用者來自于哪些學校，請從使用者資訊表中取出學校的去重資料
示例:user_profile
mysql>SELECT DISTINCT university FROM user_profile;
🚩🚩根據示例，查詢傳回以下結果
🚩🚩小貼士：
SQL中關鍵詞distinct去重：
英語中distinct 代表獨一無二的意思，
他在SQL表示去重的意思：比如本題中university這一列出現了兩次北京大學，
使用distinct進行去重查詢後，則北京大學隻出現一次。
distinct 通常效率較低
distinct 使用中，放在 select 後邊，對後面所有的字段的值統一進行去重
🌈🌈 拓展：
題目：現在營運需要檢視使用者的總數
select count(distinct university) from user_profile;
⛳️ 2.group by
🚩🚩 舉個栗子，現有這樣一張表 task
備注：
task_id: 任務id;
order_id: 訂單id;
start_time: 開始時間
注意：一個任務對應多條訂單
題目：列出任務總數
🚩🚩🚩🚩根據示例，查詢方法如下：
第1步：列出 task_id 的所有唯一值（去重後的記錄,null也是值）
select task_id
from Task
group by task_id;
第二步： 任務總數
select count(task_id) task_num
from (select task_id
from Task
group by task_id) tmp;
⛳️ 3.row_number 視窗函數
🚩🚩 舉個栗子，現有這樣一張表 task
備注：
task_id: 任務id;
order_id: 訂單id;
start_time: 開始時間
注意：一個任務對應多條訂單
題目：查詢整個表重複的資料
🚩🚩根據示例，查詢方法如下：
– 在支援視窗函數的 sql 中使用
select count(case when rn=1 then task_id else null end) task_num
from (select task_id
, row_number() over (partition by task_id order by start_time) rn
from Task) tmp;
🚩🚩小貼士：
MySQL8.0 中可以利用 ROW_NUMBER()，DENSE_RANK()，RANK() 三個視窗函數來實作排序
需要注意的一點是 as 後的别名，千萬不要與前面的函數名重名，否則會報錯
下面給出這三種函數實作排名的案例：
–三條語句對于上面三種排名
select xuehao,score, ROW_NUMBER() OVER(order by score desc) as row_r from scores_tb;
select xuehao,score, DENSE_RANK() OVER(order by score desc) as dense_r from scores_tb;
select xuehao,score, RANK() over(order by score desc) as r from scores_tb;
– 一條語句也可以查詢出不同排名
SELECT xuehao,score,
ROW_NUMBER() OVER w AS ‘row_r’,
DENSE_RANK() OVER w AS ‘dense_r’,
RANK() OVER w AS ‘r’
FROM
WINDOW w AS (ORDER BY scores_tb
desc);score
⛳️ 4.删除重複資料
建立測試資料
我們建立一個人員資訊表并在裡面插入一些重複的資料
CREATE TABLE Person(
id int auto_increment primary key comment ‘主鍵’,
Name VARCHAR(20) NULL,
Age INT NULL,
Address VARCHAR(20) NULL,
Sex CHAR(2) NULL
);
INSERT INTO Person(ID,Name,Age,Address,Sex)
VALUES
( 1, ‘張三’, 18, ‘北京路18号’, ‘男’ ),
( 2, ‘李四’, 19, ‘北京路29号’, ‘男’ ),
( 3, ‘王五’, 19, ‘南京路11号’, ‘女’ ),
( 4, ‘張三’, 18, ‘北京路18号’, ‘男’ ),
( 5, ‘李四’, 19, ‘北京路29号’, ‘男’ ),
( 6, ‘張三’, 18, ‘北京路18号’, ‘男’ ),
( 7, ‘王五’, 19, ‘南京路11号’, ‘女’ ),
( 8, ‘馬六’, 18, ‘南京路19号’, ‘女’ );
題目：資料庫中存在重複記錄，删除保留其中一條
我們發現除了自增長ID不同以為，有幾條其他字段都重複的資料出現
🐴 第一步：找出重複的資料
mysql>SELECT MAX(ID) ID,
Name,Age,Address,Sex
FROM Person
GROUP BY Name,Age,Address,Sex
HAVING COUNT(1)>1
🚩🚩小貼士：
HAVING将分組後統計出來的數量大于1的資料行，就是我們要找的重複資料
上面用Max函數或者Min函數均可，隻是為了保證取出來的資料的唯一性。
🐴 第二步：删除重複的資料
其實我們資料庫中最後要保留的結果就是第二步中查詢出來的資料，
我們把其他的資料删除即可。
怎麼删除呢？我們使用ID來排除。
DELETE FROM Person
WHERE EXISTS
(
SELECT * FROM (
SELECT
MAX(ID) ID,
Name,Age,Address,Sex
FROM Person
GROUP BY Name,Age,Address,Sex
HAVING COUNT(1)>1) T
WHERE Person.Name=T.Name
AND Person.Age=T.Age
AND Person.Address=T.Address
AND Person.Sex=T.Sex
AND Person.ID<T.ID
)
執行完後重新查詢Person表結果如下
馬六因為隻有一條記錄，是以沒有參與去重，直接顯示。
⛳️ 5.如何成為SQL高手
javascript:void(0)