Question

SQL中如何查询某个表中某个字段在一定时间差内出现的重复数据以及重复的次数

Answer 1

你时间差需要有一个字段记录时间，假设叫time

select distinct(column_name),count(1) as num from table_name where time between time1 and time2 having num > 1 order by num

追问

如上表中，patname字段间隔1个月的相同值都要查询出来，最好有重复次数

追答

sql ser代码如下，假设表名叫table_name （避免重名情况，我用InPCode唯一）：

select * from table_name a, table_name b where a.InPCode=b.InPCode and
datediff(mm,a.MarkOutDate,b.MarkOutDate)=1 order by a.InPCode

你要看重复次数就是
select a. InPCode ,a.patname,count(1) as num from table_name a, table_name b where a.InPCode=b.InPCode and
datediff(mm,a.MarkOutDate,b.MarkOutDate)=1 group by a. InPCode ,a.patname
order by a.InPCode