1. if exists(select * from sysobjects where name='stuInfo')
2. drop table stuInfo
3. create table stuInfo /*创建学员信息表**/
4. (
5. stuName varchar(20) not null,-- 姓名，非空
6. stuNo char(6) not null,-- 学号，非空
7. stuAge int not null,-- 年齡，int 默认为4个长度
8. stuId numeric(18,0),
9. stuSeat smallint ,-- 坐位
10. stuAddress text -- 住址 可以为空
11. )
12. -- 给stuInfo添加一列
13. alter table stuInfo add id int identity(1,1) primary key;
if exists(select * from sysobjects where name='stuInfo')
drop table stuInfo
create table stuInfo /*创建学员信息表**/
(
stuName varchar(20) not null,-- 姓名，非空
stuNo char(6) not null,-- 学号，非空
stuAge int not null,-- 年齡，int 默认为4个长度
stuId numeric(18,0),
stuSeat smallint ,-- 坐位
stuAddress text -- 住址 可以为空
)
-- 给stuInfo添加一列
alter table stuInfo add id int identity(1,1) primary key;
需求：只要数据stuName相同，则说明是两条重复的记录
以下为去重方法。三个方法。效率1 >2>3推荐使用第一条
[sql]view plaincopyprint?
1. Select * from stuinfo a where not exists(select 1 from stuinfo where stuName=a.stuName and ID<a.ID)
2. select a.* from stuinfo a join (select min(ID)ID,stuName from stuinfo group by stuName) b on a.stuName=b.stuName and a.ID=b.ID
3. select * from stuinfo a where ID=(select min(ID) from stuinfo where stuName=a.stuName)
Select * from stuinfo a where not exists(select 1 from stuinfo where stuName=a.stuName and ID<a.ID)
select a.* from stuinfo a join (select min(ID)ID,stuName from stuinfo group by stuName) b on a.stuName=b.stuName and a.ID=b.ID
select * from stuinfo a where ID=(select min(ID) from stuinfo where stuName=a.stuName)
结果