SqlServer去除重複行

標籤：

--第一個案例

CREATE TABLE t
(
    id INT IDENTITY(1,1) PRIMARY KEY,
    a VARCHAR(10),
    b VARCHAR(10)
)

--插入資料
INSERT INTO t
SELECT ‘aa‘,‘bb‘ UNION ALL
SELECT ‘a1‘,‘bgb‘ UNION ALL
SELECT ‘aa‘,‘bb‘ UNION ALL
SELECT ‘a2‘,‘bb‘ UNION ALL
SELECT ‘aa3‘,‘beeb‘ UNION ALL
SELECT ‘aa‘,‘bb‘ UNION ALL
SELECT ‘a2‘,‘bb‘

SELECT * FROM t ORDER BY a

--第一種重複資料刪除的方法(找出重複的項的最小標識，刪除不包含最小標識的資料)
DELETE t WHERE id NOT IN(
SELECT min(id) id FROM t GROUP BY a,b
)

--第二種重複資料刪除的方法（分區排序，給每個分區排序的結果給個行號，把行號大於1的刪除）
DELETE t FROM (
SELECT *,ROW_NUMBER() OVER (PARTITION BY a,b ORDER BY ID) row FROM t)
WHERE ROW>1

 

 

--第二個案例

CREATE TABLE user_student(id decimal(18,0) identity(1,1),st_name nvarchar(30),class nvarchar(10),score decimal(18,2))

insert into user_student(st_name,class,score)
values(‘張三‘,‘甲‘,‘90‘)
insert into user_student(st_name,class,score)
values(‘張四‘,‘甲‘,‘65‘)
insert into user_student(st_name,class,score)
values(‘張五‘,‘甲‘,‘88‘)
insert into user_student(st_name,class,score)
values(‘李三‘,‘乙‘,‘97‘)
insert into user_student(st_name,class,score)
values(‘李四‘,‘乙‘,‘88‘)
insert into user_student(st_name,class,score)
values(‘李五‘,‘乙‘,‘78‘)
insert into user_student(st_name,class,score)
values(‘王三‘,‘丙‘,‘86‘)
insert into user_student(st_name,class,score)
values(‘王四‘,‘丙‘,‘69‘)
insert into user_student(st_name,class,score)
values(‘王五‘,‘丙‘,‘90‘)

select * from user_student

SELECT ST_NAME,CLASS,SCORE
FROM (
SELECT Row_number() OVER(PARTITION BY CLASS ORDER BY SCORE DESC) AS NUM,*
FROM user_student
) AS T
WHERE NUM<=2 --每個班級分數前兩名的學生

DELETE user_student FROM  user_student WHERE id NOT IN (SELECT max(id) AS id FROM user_student
GROUP BY st_name,class,score) --刪除後來添加的重複資料

SqlServer去除重複行