select 序号,报警时间,报警代码 distinct 用户编号 from tb
解决方案 »
- 怎么一下子修改所有的字段
- Vb + sql的存贮过程返回信息问题:
- 致歉: 由于《T-SQL 2 Tips》有错,请相关用户速打此补丁: K4-911 !
- 哎,没办法只好来麻烦大家了,如何用sql语句求出不重复字段为最大的纪录?
- 路过的朋友请进来看看,请帮忙解答个问题
- 不同用户在各自的前台执行后台同一个存储过程和触发器器产生冲突吗?
- 数据库中某些字段隐含了 \r\n 这样的字符,把数据拷贝出来是类似空格的一段东西,返回xml时就变成\r\n,如何replace
- "疑难杂症,请对症下药"
- 哪位大虾对sql优化有研究,可不可以提供一些资料
- 请问在查询中如何处理MONEY类型字段?
- DISTINCT问题 在线马上结贴
- 用触发器如何清除木马,我现在只能清除一个木马,能不能通用一些?
where [编号] not in (select max([编号]) from 数据表 group by [编号])最后保留的是每个编号的最新一条数据!
WHERE EXISTS(SELECT 1 FROM LI A WHERE A.编号 = 编号)
select distinct * into #temp from 表
truncate table 表
insert 表 select * from #temp
drop table
--<二>
带有标识列
--备份数据
select * into #temp from 表
alter table #temp drop column id--删除原表数据
truncate table msgtable--恢复数据并去掉重复数据
insert into 表 select distinct * from #temp--<三>
delete From aa where a in ( select a From aa group by a having count(a)>1)
--<四>
alter table 表 add newfield int identity(1,1)delete 表
where newfield not in(select min(newfield) from 表 group by 除newfield外的所有字段)alter table 表 drop column newfield
--<五>
--添加一个处理的标识字段
alter table 表 add id int identity(1,1)
go
--删除重复记录
delete a
from 表 a left join(select id=min(id) from 表 group by a,b)b on a.id=b.idwhere b.id is null
go--删除处理用的标识字段
alter table 表 drop column id
--参考:
/*
一张表里面以两个字段为唯一字段,当几条记录的这两个字段完全相同时,需要删除重复项,如下表
a b c d
1 2 3 4
1 5 3 5
1 2 7 9
以a、b为唯一字段,第一条和第三条的a、b完全相同,所以,需要删除第一条记录1 2 3 4 或者第三条记录1 2 7 9
即如下结果:
a b c d
1 2 3 4
1 5 3 5
或
a b c d
1 5 3 5
1 2 7 9
请问各位大侠这种sql语句怎么写 */
CREATE TABLE Tb1(id int, [a] varchar(255), [b] varchar(255), [c] varchar(255), [d] varchar(255))
INSERT Tb1(id, [a], [b], [c], [d])
SELECT 1, '1','2','3','4'
UNION ALL SELECT 2, '1','5','3','5'
UNION ALL SELECT 3, '1','2','7','9'
UNION ALL SELECT 4, '1','4','7','6'delete Tb1 where [id] not in (select max([id]) from Tb1 group by a,b )
select * from tb1drop table tb1如果要同时删除第一和第三行
即如下结果:
a b c d
1 5 3 5语句如下:delete m from tb t
inner join
(
select a ,b
from tb
group by a , b
having count(*)>1
)n
on m.a = n.a and m.b = n.b
或
delete * from tb as m,
(
select a ,b
from tb
group by a , b
having count(*)>1
)n
where m.a = n.a and m.b = n.b
--在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢?谢谢!
1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录
delete from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)3、查找表中多余的重复记录(多个字段)
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录
delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)