表A,两个字段 A 整型 ; datatime datatime型regno datetime
01 2007-4-25 17:06:47.356
02 2007-3-1 12:11:47.211
01 2007-4-25 12:06:01.350
02 2007-3-7 12:18:47.219
03 2007-3-7 12:18:47.219
01 2007-4-9 00:06:01.000两个语句:
1、查询同一个regno ,一天内多次的出现的记录
期望结果:
01 2007-4-25 17:06:47.356
01 2007-4-25 12:06:01.350
2、查询同一个regno 一月内的多次出现的记录01 2007-4-25 17:06:47.356
01 2007-4-25 12:06:01.350
01 2007-4-9 00:06:01.000
01 2007-4-25 17:06:47.356
02 2007-3-1 12:11:47.211
01 2007-4-25 12:06:01.350
02 2007-3-7 12:18:47.219
03 2007-3-7 12:18:47.219
01 2007-4-9 00:06:01.000两个语句:
1、查询同一个regno ,一天内多次的出现的记录
期望结果:
01 2007-4-25 17:06:47.356
01 2007-4-25 12:06:01.350
2、查询同一个regno 一月内的多次出现的记录01 2007-4-25 17:06:47.356
01 2007-4-25 12:06:01.350
01 2007-4-9 00:06:01.000
from 表A
where regno in
(select regno from 表A group by regno,convert(varchar(10),[datetime],120) having Count(*)>1)
from 表A
where regno in
(select regno from 表A group by regno,convert(varchar(7),[datetime],120) having Count(*)>1)
group by [regno],convert(varchar(10),[datetime],120) having count(*)>1
) b where ltrim(regno)+convert(varchar(10),[datetime],120)=b.Aselect 表.* from 表,(select ltrim([regno])+convert(varchar(7),[datetime],120) A from 表
group by [regno],convert(varchar(10),[datetime],120) having count(*)>1
) b where ltrim(regno)+convert(varchar(7),[datetime],120)=b.A