一次SQL如何查重及去重的實戰(zhàn)記錄
前言
在使用SQL提數(shù)的時候,常會遇到表內(nèi)有重復(fù)值的時候,就需要做去重,本文歸類了常用方法。
?? 1.distinct
題目:現(xiàn)在運營需要查看用戶來自于哪些學(xué)校,請從用戶信息表中取出學(xué)校的去重數(shù)據(jù)
示例:user_profile
mysql>SELECT DISTINCT university FROM user_profile;
????根據(jù)示例,查詢返回以下結(jié)果
????小貼士:
SQL中關(guān)鍵詞distinct去重:
英語中distinct 代表獨一無二的意思,
他在SQL表示去重的意思:比如本題中university這一列出現(xiàn)了兩次北京大學(xué),
使用distinct進行去重查詢后,則北京大學(xué)只出現(xiàn)一次。
distinct 通常效率較低
distinct 使用中,放在 select 后邊,對后面所有的字段的值統(tǒng)一進行去重
???? 拓展:
題目:現(xiàn)在運營需要查看用戶的總數(shù)
select count(distinct university) from user_profile;
?? 2.group by
???? 舉個栗子,現(xiàn)有這樣一張表 task
備注:
task_id: 任務(wù)id;
order_id: 訂單id;
start_time: 開始時間
注意:一個任務(wù)對應(yīng)多條訂單
題目:列出任務(wù)總數(shù)
????????根據(jù)示例,查詢方法如下:
第1步:列出 task_id 的所有唯一值(去重后的記錄,null也是值)
select task_id from Task group by task_id;
第二步: 任務(wù)總數(shù)
select count(task_id) task_num from (select task_id from Task group by task_id) tmp;
?? 3.row_number 窗口函數(shù)
???? 舉個栗子,現(xiàn)有這樣一張表 task
備注:
task_id: 任務(wù)id;
order_id: 訂單id;
start_time: 開始時間
注意:一個任務(wù)對應(yīng)多條訂單
題目:查詢整個表重復(fù)的數(shù)據(jù)
????根據(jù)示例,查詢方法如下:
– 在支持窗口函數(shù)的 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() 三個窗口函數(shù)來實現(xiàn)排序
需要注意的一點是 as 后的別名,千萬不要與前面的函數(shù)名重名,否則會報錯
下面給出這三種函數(shù)實現(xiàn)排名的案例:
–三條語句對于上面三種排名
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.刪除重復(fù)數(shù)據(jù)
創(chuàng)建測試數(shù)據(jù)
我們創(chuàng)建一個人員信息表并在里面插入一些重復(fù)的數(shù)據(jù)
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號', ‘女' );
題目:數(shù)據(jù)庫中存在重復(fù)記錄,刪除保留其中一條
我們發(fā)現(xiàn)除了自增長ID不同以為,有幾條其他字段都重復(fù)的數(shù)據(jù)出現(xiàn)
?? 第一步:找出重復(fù)的數(shù)據(jù)
mysql>SELECT MAX(ID) ID, Name,Age,Address,Sex FROM Person GROUP BY Name,Age,Address,Sex HAVING COUNT(1)>1
????小貼士:
HAVING將分組后統(tǒng)計出來的數(shù)量大于1的數(shù)據(jù)行,就是我們要找的重復(fù)數(shù)據(jù)
上面用Max函數(shù)或者Min函數(shù)均可,只是為了保證取出來的數(shù)據(jù)的唯一性。
?? 第二步:刪除重復(fù)的數(shù)據(jù)
其實我們數(shù)據(jù)庫中最后要保留的結(jié)果就是第二步中查詢出來的數(shù)據(jù),
我們把其他的數(shù)據(jù)刪除即可。
怎么刪除呢?我們使用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 )
執(zhí)行完后重新查詢Person表結(jié)果如下
馬六因為只有一條記錄,所以沒有參與去重,直接顯示。
總結(jié)
到此這篇關(guān)于SQL如何查重及去重的文章就介紹到這了,更多相關(guān)SQL查重去重內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
解析mysql中:單表distinct、多表group by查詢?nèi)コ貜?fù)記錄
本篇文章是對mysql中的單表distinct、多表group by查詢?nèi)コ貜?fù)記錄進行了詳細(xì)的分析介紹,需要的朋友參考下2013-06-06mysql err錯誤提示You need to use --log-
今天在檢查mysql服務(wù)器錯誤日志的時候發(fā)現(xiàn)You need to use --log-bin to make --binlog-format work,通過如下方法解決了特分享一下2023-06-06MySQL正則表達式regexp_replace函數(shù)的用法實例
regexp_replace的使用非常靈活,且容易忘記,故做此筆記,下面這篇文章主要給大家介紹了關(guān)于MySQL正則表達式regexp_replace函數(shù)的用法實例,需要的朋友可以參考下2022-09-09JSP連接MySQL數(shù)據(jù)庫詳細(xì)步驟
這篇文章主要介紹了JSP連接MySQL數(shù)據(jù)庫詳細(xì)步驟,文章內(nèi)容詳細(xì)全面,且通過實例進行講解,容易理解,需要的朋友可以參考下2023-01-01