这个问题已经搞定了,其实想明白了很简单的,刚开始想的太复杂了,语句: select idxno,max( mq_lsh) mq_lsh from saptomz:o_tpc08_ordermst where makedate=today-1 group by idxno 没了,不过还是谢谢回复我的朋友们,谢谢你们!!
if OBJECT_ID('tempdb..#temp', 'u') is not null drop table #temp; CREATE TABLE #temp(idxno varchar(100), mq_lsh int, mq_sendtime datetime) insert #temp select '5000060376','27149','2013-08-14 23:05:56' union all select '5000060376','27182','2013-08-14 23:06:06' union all select '5000060377','27150','2013-08-14 23:05:56' union all select '5000060377','27183','2013-08-14 23:06:06' union all select '5000060378','27151','2013-08-14 23:05:56' union all select '5000060378','27184','2013-08-14 23:06:06' union all select '5000060379','27152','2013-08-14 23:05:56' union all select '5000060379','27185','2013-08-14 23:06:06' --如果2005及以上的话 ;WITH cte AS ( SELECT rowid=ROW_NUMBER() OVER(PARTITION BY idxno ORDER BY mq_sendtime, mq_lsh), * FROM #temp ) delete FROM cte WHERE rowid <> 1SELECT * FROM #temp /* idxno mq_lsh mq_sendtime 5000060376 27149 2013-08-14 23:05:56.000 5000060377 27150 2013-08-14 23:05:56.000 5000060378 27151 2013-08-14 23:05:56.000 5000060379 27152 2013-08-14 23:05:56.000 */
先查询要删除的记录: select * from tb where mq_lsh mq_sendtime not in( select min( mq_lsh mq_sendtime) as mq_lsh mq_sendtime from tb group by idxno)如果正确,再删除 delete from tb where mq_lsh mq_sendtime not in( select min( mq_lsh mq_sendtime) as mq_lsh mq_sendtime from tb group by idxno)
idxno mq_lsh mq_sendtime5000060376 27149 2013-08-14 23:05:56
5000060376 27182 2013-08-14 23:06:06
5000060377 27150 2013-08-14 23:05:56
5000060377 27183 2013-08-14 23:06:06
5000060378 27151 2013-08-14 23:05:56
5000060378 27184 2013-08-14 23:06:06
5000060379 27152 2013-08-14 23:05:56
5000060379 27185 2013-08-14 23:06:06如上所示:我想删除重复的保留重复的一条就可以,这样的SQL怎么写?跪求
select idxno,max( mq_lsh) mq_lsh
from saptomz:o_tpc08_ordermst
where makedate=today-1
group by idxno
没了,不过还是谢谢回复我的朋友们,谢谢你们!!
CREATE TABLE #temp(idxno varchar(100), mq_lsh int, mq_sendtime datetime)
insert #temp
select '5000060376','27149','2013-08-14 23:05:56' union all
select '5000060376','27182','2013-08-14 23:06:06' union all
select '5000060377','27150','2013-08-14 23:05:56' union all
select '5000060377','27183','2013-08-14 23:06:06' union all
select '5000060378','27151','2013-08-14 23:05:56' union all
select '5000060378','27184','2013-08-14 23:06:06' union all
select '5000060379','27152','2013-08-14 23:05:56' union all
select '5000060379','27185','2013-08-14 23:06:06'
--如果2005及以上的话
;WITH cte AS
(
SELECT rowid=ROW_NUMBER() OVER(PARTITION BY idxno ORDER BY mq_sendtime, mq_lsh), * FROM #temp
)
delete FROM cte
WHERE rowid <> 1SELECT * FROM #temp
/*
idxno mq_lsh mq_sendtime
5000060376 27149 2013-08-14 23:05:56.000
5000060377 27150 2013-08-14 23:05:56.000
5000060378 27151 2013-08-14 23:05:56.000
5000060379 27152 2013-08-14 23:05:56.000
*/
select *
from tb
where mq_lsh mq_sendtime
not in(
select min( mq_lsh mq_sendtime) as mq_lsh mq_sendtime
from tb
group by idxno)如果正确,再删除
delete from tb
where mq_lsh mq_sendtime
not in(
select min( mq_lsh mq_sendtime) as mq_lsh mq_sendtime
from tb
group by idxno)