K07
关联字段 日期 时间 机号
a0188, k0700, k0701, k0702102 2011-05-07 08:00 2
102 2011-05-07 08:00 2
102 2011-05-07 08:01 2
103 2011-05-07 12:01 3
103 2011-05-07 12:01 3
结果:
102 2011-05-07 08:00 2
102 2011-05-07 08:01 2
103 2011-05-07 12:01 3
关联字段 日期 时间 机号
a0188, k0700, k0701, k0702102 2011-05-07 08:00 2
102 2011-05-07 08:00 2
102 2011-05-07 08:01 2
103 2011-05-07 12:01 3
103 2011-05-07 12:01 3
结果:
102 2011-05-07 08:00 2
102 2011-05-07 08:01 2
103 2011-05-07 12:01 3
from tb
create table #K07
(a0188 int, k0700 datetime, k0701 nvarchar(10), k0702 int) --关联字段 日期 时间 机号
insert #K07
select 102,'2011-05-07','08:00', 2 union all
select 102,'2011-05-07','08:00', 2 union all
select 102,'2011-05-07','08:01', 2 union all
select 103,'2011-05-07','12:01', 3 union all
select 103,'2011-05-07','12:01', 3--樓主是要SELETE 還是DELETE ?
--SELETE
select distinct a0188 , k0700 , k0701 , k0702 from #K07--a0188 k0700 k0701 k0702
------------- ----------------------- ---------- -----------
--102 2011-05-07 00:00:00.000 08:00 2
--102 2011-05-07 00:00:00.000 08:01 2
--103 2011-05-07 00:00:00.000 12:01 3
--
--(3 row(s) affected)--DELETE
;with T as(select Row_number()over(partition by a0188 , k0700 , k0701 , k0702 order by getdate()) as num,
a0188 , k0700 , k0701 , k0702 from #K07)
delete T where num<>1select * from #K07--a0188 k0700 k0701 k0702
------------- ----------------------- ---------- -----------
--102 2011-05-07 00:00:00.000 08:00 2
--102 2011-05-07 00:00:00.000 08:01 2
--103 2011-05-07 00:00:00.000 12:01 3
--
--(3 row(s) affected)
select distinct a0188,k0700,k0701,k0702
from k07
select a0188, k0700, k0701, k0702
from tabname
group by a0188, k0700, k0701, k0702
having count(1)>1
create table #K07
(a0188 int, k0700 datetime, k0701 nvarchar(10), k0702 int) --关联字段 日期 时间 机号
insert #K07
select 102,'2011-05-07','08:00', 2 union all
select 102,'2011-05-07','08:00', 2 union all
select 102,'2011-05-07','08:01', 2 union all
select 103,'2011-05-07','12:01', 3 union all
select 103,'2011-05-07','12:01', 3--求重複
select * from #K07 group by a0188,k0700,k0701,k0702 having count(1)>1
--找出重复的记录
select c1,c2,c3,c4
from tb
group by c1,c2,c3,c4
having count(1) > 1--删除重复记录
select distinct * into #tb
from tbdelete from tbinsert into tb select * from #tbdrop table #tb
2.select * from tb group by a0188,k0700,k0701,k0702 having count(*)>=2