一次SQL怎么查重及去重

這篇文章主要為大家展示了“一次SQL怎么查重及去重”，內容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領大家一起研究并學習一下“一次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 scores_tb
WINDOW w AS (ORDER BY score desc);

?? 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表結果如下

馬六因為只有一條記錄，所以沒有參與去重，直接顯示。

以上是“一次SQL怎么查重及去重”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！