SQL语句 导入EXCEL 剔除相同数据

--调用GGGGG数据库

use GGGGG
go

--全局配置设置

exec sp_configure 'show advanced options',1     --打开高级设置
reconfigure --初始化设置
exec sp_configure 'Ad Hoc Distributed Queries',1  --启用分布式查询
reconfigure --初始化设置
go

--导入EXCEL 到Student表

insert into Student
select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=F:\Student.xls',sheet1$)  
go

--查询以剔除的数据
select * from Student s
where not exists (
select Student.Sid from Student where Student.Sname=s.Sname and Student.Ssex =s.Ssex
and Student.Sadress=s.Sadress and Student.Sage=s.Sage and
Student.Sclass=s.Sclass and Student.Sid<>s.Sid
)
--查询重复的数据
select * from Student s
where exists (
select * from Student where Student.Sname=s.Sname and Student.Ssex =s.Ssex
and Student.Sadress=s.Sadress and Student.Sage=s.Sage and
Student.Sclass=s.Sclass and Student.Sid<>s.Sid
)

--此处1为任意常数，执行效率较高

select * from Student s
where exists (
select 1 from Student where Student.Sname=s.Sname and Student.Ssex =s.Ssex
and Student.Sadress=s.Sadress and Student.Sage=s.Sage and
Student.Sclass=s.Sclass and Student.Sid<>s.Sid
)