怎么去掉刷卡重复的数据

[pengpeng409]

K07


关联字段 日期 时间 机号
a0188, k0700, k0701, k0702

102 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

[X_0]

select distinct a0188,k0700,k0701,k0702
from tb

[liang145]

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<>1

select * 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)

[jyh070207]

select distinct a0188,k0700,k0701,k0702
from k07

[pengpeng409]

那假设我想找出重复的数据：
效果：
102 2011-05-07 08:00 2
103 2011-05-07 12:01 3
怎么查询 ？

[oO寒枫Oo]

select a0188, k0700, k0701, k0702
from tabname
group by a0188, k0700, k0701, k0702
having count(1)>1

[liang145]

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

[AcHerat]

--找出重复的记录
select c1,c2,c3,c4
from tb
group by c1,c2,c3,c4
having count(1) > 1

--删除重复记录
select distinct * into #tb
from tb

delete from tb

insert into tb select * from #tb

drop table #tb

[chuanzhang5687]

1.select distinct * from tb
2.select * from tb group by  a0188,k0700,k0701,k0702 having count(*)>=2

[lanyuxcm]

[Quote=引用 2 楼 liang145 的回复:]
SQL code

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……
[/Quote]
支持