-- Call the ggggg Database
Use ggggg
Go
-- Global configuration settings
Exec sp_configure 'show advanced options', 1-- Enable advanced settings
Reconfigure-- Initialization settings
Exec sp_configure 'ad hoc distributed queries ', 1-- Enable Distributed Query
Reconfigure-- Initialization settings
Go
-- Import EXCEL to student table
Insert into student
Select * from
OpenRowSet ('Microsoft. Jet. oledb.4.0 ', 'excel 5.0; HDR = yes; database = F: \ student.xls', sheet1 $)
Go
-- Query the data to be excluded
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
)
-- Query duplicate data
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
)
-- Here 1 is an arbitrary constant and the execution efficiency is high.
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
)