写这个问题是最近我参加过一次面试 主考官问我一个经典的 重复数据 查询 删除的问题。面试完后,我感觉这次面试相当失败,具体不说了,看下面的问题吧。
以下SQL 在SQL server 2008 管理平台下
表很简单
CREATE TABLE [dbo].[user](
[id] [int] ,
[name] [nvarchar](50)
)
用户表 2个列 一个列ID 一个是ID的姓名 没有主键 ,当然有主键的情况另外说
先看没有主键的情况 ,这种情况的数据非常乱,这个表的数据完全可能有以下情况
insert into user values('1','x')
insert into user values('1','x')
insert into user values('2','x')
insert into user values('2','x')
insert into user values('3','x')
insert into user values('3','x')
insert into user values('1','y')
insert into user values('1','y')
insert into user values('2','y')
insert into user values('2','y')
insert into user values('3','y')
insert into user values('3','y')
有个人x 他的id 1,2,3 并且ID是重复 y 和x 一样的情况
现在要你删除这些重复的值 但是同一个name 只需要保留一个最大的那个ID
结果就是
ID NAME
3 x
3 y
不允许创建额外的临时表 中间表 只用select 语句,想了下 比较有难度 说了这么说其实就是 两个(多个)一模一样的记录
你需要删其中的N条但是只要保留其中的一条
select * from
(select id,name,ROW_NUMBER() over(partition by name order by id desc ) as mc from user ) t
where t.mc>'1'
或者 desc asc 降序 升序
select * from
(select id,name,ROW_NUMBER() over(partition by name order by id desc ) as mc from user ) t
where t.mc='1'
数据查询出来了 现在是如何去删除重复的数据
尝试写了下面几个查询
select * from user a where a.ID<(select MAX(id) from user b where a.name=B.name )
select * from user a where a.ID=(select MAX(id) from user b where a.name=B.name )
查询效果不一样
对于一个x 对应 11,22,33 这个语句保留一些重复的 把 select * 改成 delete 删除的是小于的 但是等于的重复数据还是保留了
删除没有删完整 对于第一种 能查询出来 但是删除的SQL
WITH XX AS
(
select * from
(select id,name,ROW_NUMBER() over(partition by name order by id desc) as mc from user ) t
where t.mc>1'
)
SELECT * FROM XX
先看查询结果
然后 把 select * 改delete 能够实现删除重复数据
关键是 涉及到 row_number() rank() over(partition by order by ) 排序 语句介绍
比较经典的并且好记得 我觉得是 许多个班级,要你计算每个班级分数前几名的排序 计算第一名 第二名
然后要你查询出每个班级的名次情况 等等