1:
Select a.* from student a
join (select XingM,KeM,ChengJ from student group by XingM,KeM,ChengJ having count(*) > 1) b on a.XingM = b.XingM and a.KeM = b.KeM and a.ChengJ = b.ChengJ
2:
Delete student a where id <>
(select Min(id) from student where XingM = a.XingM and KeM = a.KeM and ChengJ = a.ChengJ)
Select a.* from student a
join (select XingM,KeM,ChengJ from student group by XingM,KeM,ChengJ having count(*) > 1) b on a.XingM = b.XingM and a.KeM = b.KeM and a.ChengJ = b.ChengJ
2:
Delete student a where id <>
(select Min(id) from student where XingM = a.XingM and KeM = a.KeM and ChengJ = a.ChengJ)
group by XingM,KeM,ChengJ
having count(*)>1
group by XingM,KeM,ChengJ
having count(*)>1
delete from student a where id<>(select min(id) from student b where b.Xingm=a.Xingm and b.Kem=a.Kem and b.Chengj=a.Chengj)
go
group by XingM,KeM,ChengJ
having count(*)>1
2 delete from student where
id not in (select min(id) from student group by XingM,KeM,ChengJ)
insert into ai select 1, '张三' , '语文', 80
insert into ai select 2, '张三' , '数学' , 90
insert into ai select 3, '张三' , '英语' , 70
insert into ai select 4, '张三' , '语文' , 80
insert into ai select 5, '张三', '数学' , 90
问题1:要求用一条SQL语句显示出所有后三列重复的行;(最好能把字段id列也显示)
问题2:删除重复行,只保留一行记录。(类似第4、5行需要删掉)1.select min(id) id,XingM ,KeM ,ChengJ
from ai
group by XingM ,KeM ,ChengJ
order by 12.
delete from ai
where id not in (
select min(id)
from ai
group by XingM ,KeM ,ChengJ)