CREATE TABLE [dbo].[T_class] ( [学生ID] [char] (10) COLLATE Chinese_PRC_CI_AS NULL , [学生姓名] [char] (10) COLLATE Chinese_PRC_CI_AS NULL , [课程ID] [char] (10) COLLATE Chinese_PRC_CI_AS NULL , [课程名称] [char] (10) COLLATE Chinese_PRC_CI_AS NULL , [成绩] [float] NULL , [教师ID] [char] (10) COLLATE Chinese_PRC_CI_AS NULL , [教师姓] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GO 生成表脚本如上,查询语句如下,我测试过了,OKselect * from T_class where (select count(*) from T_class as tmpclass where tmpclass.学生ID=T_class.学生ID)>1 学生ID为主键
CREATE TABLE [dbo].[T_class] ( [学生ID] [char] (10) COLLATE Chinese_PRC_CI_AS NULL , [学生姓名] [char] (10) COLLATE Chinese_PRC_CI_AS NULL , [课程ID] [char] (10) COLLATE Chinese_PRC_CI_AS NULL , [课程名称] [char] (10) COLLATE Chinese_PRC_CI_AS NULL , [成绩] [float] NULL , [教师ID] [char] (10) COLLATE Chinese_PRC_CI_AS NULL , [教师姓] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GO 生成表脚本如上,查询语句如下,我测试过了,OKselect * from T_class where (select count(*) from T_class as tmpclass where tmpclass.学生ID=T_class.学生ID)>1 学生ID为主键
如果相同的记录只显示一行,可以如下:select max(field1) from table having cout(*)>1
UP如果重复记录只显示一条select max(f1) from table having count(*)>1与主键无关,但是要求显示字段可以使用Max
IF no ID:select distinct a,b,c,d into #T1 from table1 select x.a,x.b,x.c,x.d from table1 x,#t1 y where x.a<>y.a and x.b<>y.b and x.c<>y.c and x.d<>y.dor select a.*,identity(int,1,1) as ID into #T1 from table1 a select a,b,c,d from #t1 where id not in (select max(ID) from #t1 group by a,b,c,d)
IF no ID:select distinct a,b,c,d into #T1 from table1 select x.a,x.b,x.c,x.d from table1 x,#t1 y where x.a<>y.a and x.b<>y.b and x.c<>y.c and x.d<>y.dor select a.*,identity(int,1,1) as ID into #T1 from table1 a select a,b,c,d from #t1 where id not in (select max(ID) from #t1 group by a,b,c,d)
select a.*,identity(int,1,1) as ID into #T1 from table1 a select a,b,c,d from #t1 x where id < (select max(ID) from #t1 where a=x.a and b=x.b and c=x.c and d=x.d)
select * from (select count(id) i_count, item1, item2, ... from tablename group by item1, item2, ... ) as A where A.i_count > 1
[学生ID] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[学生姓名] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[课程ID] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[课程名称] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[成绩] [float] NULL ,
[教师ID] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[教师姓] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
生成表脚本如上,查询语句如下,我测试过了,OKselect * from T_class where (select count(*) from T_class as tmpclass where tmpclass.学生ID=T_class.学生ID)>1
学生ID为主键
[学生ID] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[学生姓名] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[课程ID] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[课程名称] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[成绩] [float] NULL ,
[教师ID] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[教师姓] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
生成表脚本如上,查询语句如下,我测试过了,OKselect * from T_class where (select count(*) from T_class as tmpclass where tmpclass.学生ID=T_class.学生ID)>1
学生ID为主键
select x.a,x.b,x.c,x.d from table1 x,#t1 y
where x.a<>y.a and x.b<>y.b and x.c<>y.c and x.d<>y.dor
select a.*,identity(int,1,1) as ID into #T1 from table1 a
select a,b,c,d from #t1
where id not in (select max(ID) from #t1 group by a,b,c,d)
select x.a,x.b,x.c,x.d from table1 x,#t1 y
where x.a<>y.a and x.b<>y.b and x.c<>y.c and x.d<>y.dor
select a.*,identity(int,1,1) as ID into #T1 from table1 a
select a,b,c,d from #t1
where id not in (select max(ID) from #t1 group by a,b,c,d)
select a,b,c,d from #t1 x
where id < (select max(ID) from #t1 where a=x.a and b=x.b and c=x.c and d=x.d)
(select count(id) i_count, item1, item2, ... from tablename
group by item1, item2, ... ) as A
where A.i_count > 1