ps:今天去面试被这个问题考倒了,sql要恶补一下了。
选择重复,消除重复和选择出序列
有例表:emp
emp_no name age
001 Tom 17
002 Sun 14
003 Tom 15
004 Tom 16
要求:
列出所有名字重复的人的记录
(1)最直观的思路:要知道所有名字有重复人资料,首先必须知道哪个名字重复了:
select name from emp group by name having count(*)>1
所有名字重复人的记录是:
select * from emp
where name in (select name from emp group by name having count(*)>1)
(2)稍微再聪明一点,就会想到,如果对每个名字都和原表进行比较,大于2个人名字与这条记录相同的就是合格的 ,就有
select * from emp where (select count(*) from emp e where e.name=emp.name) >1
--注意一下这个>1,想下如果是 =1,如果是 =2 如果是>2 如果 e 是另外一张表 而且是=0那结果 就更好玩了:)
这个过程是 在判断工号为001的 人 的时候先取得 001的 名字(emp.name) 然后和原表的名字进行比较 e.name
注意e是emp的一个别名。
再稍微想得多一点,就会想到,如果有另外一个名字相同的人工号不与她他相同那么这条记录符合要求:
select * from emp
where exists
(select * from emp e where e.name=emp.name and e.emp_no<>emp.emp_no)
此思路的join写法:
select emp.* from emp,emp e
where emp.name=e.name and emp.emp_no<>e.emp_no
/* 这个语句较规范的 join 写法是
select emp.* from emp inner join emp e on emp.name=e.name and emp.emp_no<>e.emp_no
但个人比较倾向于前一种写法,关键是更清晰 */
b、有例表:emp
name age
Tom 16
Sun 14
Tom 16
Tom 16
----------------------------------------------------清除重复----------------------------------------------------
过滤掉所有多余的重复记录
(1)我们知道distinct、group by 可以过滤重复,于是就有最直观的
select distinct * from emp 或 select name,age from emp group by name,age
获得需要的数据,如果可以使用临时表就有解法:
select distinct * into #tmp from emp
delete from emp
insert into emp select * from #tmp
(2)但是如果不可以使用临时表,那该怎么办?
我们观察到我们没办法区分数据(物理位置不一样,对 SQL Server来说没有任何区别),思路自然是想办法把数据区分出来了,既然现在的所有的列都没办法区分数据,唯一的办法就是再加个列让它区分出来,加什么列好?最佳选择是identity列:
alter table emp add chk int identity(1,1)
表示例:
name age chk
Tom 16 1
Sun 14 2
Tom 16 3
Tom 16 4
重复记录可以表示为:
select * from emp where (select count(*) from emp e where e.name=emp.name)>1
要删除的是:
delete from emp
where (select count(*) from emp e where e.name=emp.name and e.chk>=emp.chk)>1
再把添加的列删掉,出现结果。
alter table emp drop column chk
(3)另一个思路:
视图
select min(chk) from emp group by name having count(*) >1
获得有重复的记录chk最小的值,于是可以
delete from emp where chk not in (select min(chk) from emp group by name)
写成join的形式也可以:
(1)有例表:emp
emp_no name age
001 Tom 17
002 Sun 14
003 Tom 15
004 Tom 16
◆要求生成序列号
(1)最简单的方法,根据b问题的解法:
alter table emp add chk int identity(1,1) 或
select *,identity(int,1,1) chk into #tmp from emp
◆如果需要控制顺序怎么办?
select top 100000 *,identity(int,1,1) chk into #tmp from emp order by age
(2) 假如不可以更改表结构,怎么办?
如果不可以唯一区分每条记录是没有办法的,在可以唯一区分每条记录的时候,可以使用a 中的count的思路解决这个问题
select emp.*,(select count(*) from emp e where e.emp_no<=emp.emp_no)
from emp
order by (select count(*) from emp e where e.emp_no<=emp.emp_no)
分享到:
相关推荐
经常面试到的SQL题(查询重复数据和清除重复数据).
经常面试到的SQL题(查询重复数据和清除重复数据).
轻松消除SQL SERVER重复记录,大家一起来看看吧。。。。。。。。。。。。。。。。。。。。。。。。。。。
SQL删除表里的重复数据的两种方法! 值得下载看看!资源免费,大家分享!! 更多免费资源 http://ynsky.download.csdn.net/
根据autoID删除临时表#tmp中的重复数据,只保留每组重复数据中的第一条
一条SQL清除(单字段、多字段)重复数据的办法.txt
消除重复行的话,必须指定关键词 distinct SELECT distinct 定额名称 FROM [材料进货明细表$] 2、查询满足条件的元组 例5、查询材料进货明细表中单位为“套”的所有记录 SELECT * FROM [材料进货明细表$] where ...
主要介绍了sql2000数据库清除重复数据的二种方法,可以使用使用游标实现和sql语句实现,需要的朋友可以参考下
9.7 查询计划Hash和查询Hash 248 9.8 执行计划缓冲建议 251 9.8.1 明确地参数化查询的可变部分 252 9.8.2 使用存储过程实现业务功能 252 9.8.3 使用sp_executesql编程以避免存储过程维护 252 9.8.4 实现准备/...
SQL重复记录查询 1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断 select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1...
9.7 查询计划Hash和查询Hash 248 9.8 执行计划缓冲建议 251 9.8.1 明确地参数化查询的可变部分 252 9.8.2 使用存储过程实现业务功能 252 9.8.3 使用sp_executesql编程以避免存储过程维护 252 9.8.4 实现准备/...
使用Distinct关键字可以消除重复记录。 Distinct关键字可从SQL 语句结果中除去重复的行,如果没有指定DISTINCT关键字,那么将返回所有行,包括重复的行。 在使用DISTINCT关键字去除重复记录时,需将DISTINCT...
相同查询返回SQL查询结果将仅针对当前请求进行缓存。 缓存将在请求结束时清除。 正在安装 $ pip install django-sql-caching 然后将sql_caching.middleware.QueryCacheMiddleware添加到您的MIDDLEWARE_CLASSES 。 ...
11.9.3 My SQL Server和DB2中递归查询 附录A 常用数据库系统的安装和使用 A.1 DB2的安装和使用 A.2 MySQL的安装和使用 A.3 Oracle的安装和使用 A.4 Microsoft SQL Server的安装和使用
11.9.3 My SQL Server和DB2中递归查询 附录A 常用数据库系统的安装和使用 A.1 DB2的安装和使用 A.2 MySQL的安装和使用 A.3 Oracle的安装和使用 A.4 Microsoft SQL Server的安装和使用 显示部分信息
--收缩数据和日志 DBCC SHRINKDB DBCC SHRINKFILE 3、压缩数据库 dbcc shrinkdatabase(dbname) 4、转移数据库给新用户以已存在用户权限 exec sp_change_users_login 'update_one','newname','oldname' go 5、检查...
出现重复主键的情形之一是,在 SQL Server 外部的非关系数据中存在重复的 PK,在导入这些数据时没有强制执行 PK 唯一性。出现重复主键的另一种情形来自数据库设计错误,如没有对每张表强制执行实体完整性。
查询数据指从数据库中获取所需要的数据。查询数据是数据库操作中最常用,也是最重要的操作。用户可以根据自己对数据的需求,使用不同的查询方式。通过不同的查询方式,可以获得不同的数据。MySQL中是使用SELECT语句...
3.8识别和消除笛卡儿积 3.9聚集与联接 3.10聚集与外联接 3.11从多个表中返回丢失的数据 3.12在运算和比较时使用NULL值 第4章 插入、更新与删除 4.1插入新记录 4.2插入默认值 4.3使用NULL代替默认值 4.4从...