select * from tb t where exists(
select 1 from tb where id=t.id group by id having count(1)>1
)1
select 1 from tb where id=t.id group by id having count(1)>1
)1
解决方案 »
- 安装不上SQL2005问题
- 谁来帮我分析下这几个引号的作用哈
- 通过备份文件把数据导入一个新的数据库,只有数据没有主外键等约束关系。(Sql Server 2005)
- 在表中,如何得到不在当前年月的所有记录?
- ASP提交ADO Connection.Execute 后没有执行?
- 安装server2000是的几个问题,,请高手指教》》》》》
- 高分求助时间点恢复的问题
- 同一个表中不同纪录不同字段的相同值(30分)
- 谁有ODBC FOR SQL SERVER2000的驱动程序?多谢!(在线等待,立即给分)
- 急!在线等待!sql语句问题
- 数据库还原问题!!急急急~~~~~
- 正在进行事务回滚。估计回滚已完成: 0%。估计剩余时间: 0 秒,这是怎么回事???
select 1 from tb where id=t.id and name<t.name
)2
select id from info
group by id
having count(1)>2delete a from (select id,ord=(row_number() over (order by id)) from info ) a
where ord<>1
select * from info where id in (Select id from info group by id having(*)>1 )
刪除重複紀錄
delete from info where id in (Select id from info group by id having(*)>1 ) and id not in (select max(id) from info group by id)
select * from info where exists(
select 1 from info i where id=info.id group by id having count(1)>=2)--2
delete from info where exists(select 1 from info i where id=info.id and name>info.name)
name
a
b
c
d
想在让他们两两比赛,用一个语句写出他们互相比赛的可能性
-> 测试数据: #info
if object_id('tempdb.dbo.#info') is not null drop table #info
create table #info (name varchar(1))
insert into #info
select 'a' union all
select 'b' union all
select 'c' union all
select 'd'select distinct
case when a.name>b.name then a.name+b.name else b.name+a.name end
from #info as a cross join #info as b where a.name<>b.name
/*
ba
ca
cb
da
db
dc
*/
select id from info group by id having count(*) > 1--2
delete info from info t where name not in (select min(name) from info where id = t.id)
(
id int,
[name] varchar(10)
)
insert into info select 1,'aa'
insert into info select 1,'bb'
insert into info select 2,'cc'
insert into info select 2,'dd'
insert into info select 3,'ee'
insert into info select 4,'ff'
insert into info select 4,'gg'
insert into info select 2,'hh' select id from info
group by id
having count(id)>1
select id from table group by id having count(id)>1 最出重复ID
2
delete table from table t where name not in(select max(name) from table where t.id = id)
select id from table group by id having(count(id)>1)
2
delect table from table t where name not in(select max(name) from table where t.id = id)
declare @tb table(Name varchar(50))
insert into @tb
select 'a' union all
select 'b' union all
select 'c' union all
select 'd' select t1.[Name],t2.Name from @tb t1
inner join @tb t2 on (t1.Name<t2.name)
declare @tb table(id int,Name varchar(50))
insert into @tb
select 1, 'aa' union all
select 1, 'bb' union all
select 2, 'cc' union all
select 2, 'dd' union all
select 3, 'ee' union all
select 4, 'ff' union all
select 4, 'gg' union all
select 2, 'hh'--1
select * from @tb t1 where (select count(*) from @tb where id=t1.id)>1 --2
delete t from @tb t
where exists(select 1 from @tb where id=t.id and name<t.name)
2、delete from info a (select ID,Max(Name) as Name from Info group by id having count(*)>1) b
on a.ID=b.ID and a.Name<>b.Name
http://blog.csdn.net/playyuer/archive/2002/12/12/2848.aspx
select * from info where id in (select id from info group by id having count(id)>=2)
2
delete info from info A where name <>(select max(name) from info where id=a.id)