www.1862.net > SQL查询时如何去掉重复记录后保留最新的记录

SQL查询时如何去掉重复记录后保留最新的记录

Delete from tablea awhere 记录号 not in (select max(记录号) from tablea b where b.col1=a.col1 and b.col2=a.col2 ) --取出重复数据中记录号最大的,然后删除不是最大记录号的数据

测试数据: if object_id('tempdb.dbo.#') is not null drop table # create table #(姓名 varchar(8), 日期 datetime) insert into # select '张三', '2009-01-01' union all select '张三', '2010-10-10' union all select '李四', '2010-1-10...

测试数据: if object_id('tempdb.dbo.#') is not null drop table # create table #(姓名 varchar(8), 日期 datetime) insert into # select '张三', '2009-01-01' union all select '张三', '2010-10-10' union all select '李四', '2010-1-10...

1.查询出重复记录 select 重复记录字段 form 数据表 group by houseno having count(重复记录字段)>1 2.重复记录只显示一条ID值最小或最大的记录 select id,* from 数据表 where houseno (select 重复记录字段 form 数据表 group by 重复记录 ...

DELETE FROM table WHERE id IN ( SELECT id FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY item_id ORDER BY id) RID,* FROM table WHERE item_id in (10,23,12) ) A WHERE RID>1)

代码如下: DELETE FROM zkw_kehu WHERE (k_cname IN (SELECT k_cname FROM zkw_kehu GROUP BY k_cname HAVING COUNT(k_cname) > 1)) AND (k_id NOT IN (SELECT MIN(k_id) FROM zkw_kehu GROUP BY k_cname HAVING COUNT(k_cname) > 1))

你说的重复是指两条记录完成一样么,如果是则用 SELECT DISTINCT COLUMN1,COLUMN2 FROM TABLE1 或 SELECT COLUMN1,COLUMN2 FROM TABLE1 GROUP BY COLUMN1,COLUMN2 若你要查询出来的字段在两条记录中有不同值,可以用MAX() 或者可以根据你自己...

delete from table where type = 'P' and xtype = '3' and execTime not in ( select max(execTime) as execTime from table where type = 'P' and xtype = '3' ) 上述语句是基于最近修改的时间一定是所有重复数据中的最大的。

用SQL语句,删除掉重复项只保留一条 在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢 1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断 select * from people where peopleId in (select peopleId from ...

网站地图

All rights reserved Powered by www.1862.net

copyright ©right 2010-2021。
www.1862.net内容来自网络,如有侵犯请联系客服。zhit325@qq.com