--调用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
)