有一个数据库表dept中有如下数据:
id_no id_name
2001 s1
2002 s2
2003 s3
2001 s4
2001 s5
2002 s6
表中有id_no重复,如id_no为1000的有3条记录,如id_no为1001的有2条记录
现在要按照id_no给表建索引,需要删除id_no重复了的那些记录,但不能删掉所有拥有该id_no的记录,必需保留一条拥有该id_no的记录(如id_no为1000的只剩下一条记录)
(1)请写出SQl语句(或SQL语句组),查询所有id_no重复的记录。
(2)请写出SQl语句实现题目要求的结果。
id_no id_name
2001 s1
2002 s2
2003 s3
2001 s4
2001 s5
2002 s6
表中有id_no重复,如id_no为1000的有3条记录,如id_no为1001的有2条记录
现在要按照id_no给表建索引,需要删除id_no重复了的那些记录,但不能删掉所有拥有该id_no的记录,必需保留一条拥有该id_no的记录(如id_no为1000的只剩下一条记录)
(1)请写出SQl语句(或SQL语句组),查询所有id_no重复的记录。
(2)请写出SQl语句实现题目要求的结果。
select 2001,'s1'
union all select 2002, 's2'
union all select 2003 ,'s3'
union all select 2001 ,'s4'
union all select 2001 ,'s5'
union all select 2002 ,'s6'select * from table1 select table1.* into #t from table1, (select idno, min(idname) as idname from table1 group by idno) b
where (table1.idno = b.idno and table1.idname = b.idname)delete from table1insert into table1 select * from #tdrop table #t
select * from table1
FROM tindex
GROUP BY id
这就是答案,可是为什么我不加“GROUP BY id” 就说什么该列不包含在聚合函数中,请问这是为什么?谢谢
(
select id_no from test group by id_no having count(*)>1
)或者select id_no from test group by id_no having count(*)>1