declare @datestart datetime,@dateend datetime
select @datestart='2012/07/21',@dateend='2012/07/23'
select distinct [违规人] from [testtable]
    where [违规时间] between @datestart and @dateend
        and exists (
                select 1 from (select [违规人],count(1) cou from (select [违规人],[违规时间] 
                    from [testtable] 
                    where [违规时间] between @datestart and @dateend
                    group by [违规人],[违规时间]) t
                group by [违规人]) t1
                where t1.[违规人]=[testtable].[违规人] and t1.cou=datediff(day,@datestart,@dateend)+1
            );

------解决方案--------------------
SQL code

CREATE TABLE [dbo].[testtable](
[ID] [bigint] NOT NULL,
[违规人] [varchar](max) NULL,
[违规时间] [datetime] NULL
)
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[testtable] ([ID], [违规人], [违规时间]) VALUES (1, N'白晨', CAST(0x0000A09500000000 AS DateTime))
INSERT [dbo].[testtable] ([ID], [违规人], [违规时间]) VALUES (1, N'白晨', CAST(0x0000A09500000000 AS DateTime))
INSERT [dbo].[testtable] ([ID], [违规人], [违规时间]) VALUES (1, N'白晨', CAST(0x0000A09500000000 AS DateTime))
INSERT [dbo].[testtable] ([ID], [违规人], [违规时间]) VALUES (2, N'孙明', CAST(0x0000A09500000000 AS DateTime))
INSERT [dbo].[testtable] ([ID], [违规人], [违规时间]) VALUES (3, N'李岩', CAST(0x0000A09500000000 AS DateTime))
INSERT [dbo].[testtable] ([ID], [违规人], [违规时间]) VALUES (4, N'孙明', CAST(0x0000A09600000000 AS DateTime))
INSERT [dbo].[testtable] ([ID], [违规人], [违规时间]) VALUES (5, N'李岩', CAST(0x0000A09600000000 AS DateTime))
INSERT [dbo].[testtable] ([ID], [违规人], [违规时间]) VALUES (8, N'孙明', CAST(0x0000A09700000000 AS DateTime))

with t
as(
select 
    *,
    px=COUNT([违规人])over(partition by [违规人])
from 
    [testtable] a
where 
    exists(
        select 
            1 
        from 
            [testtable] b 
        where 
            a.[违规时间]<>b.[违规时间] 
            and a.[违规人]=b.[违规人]
    )
)
select 
    [ID],
    [违规人],
    [违规时间]
from
    t
where 
    px=DATEDIFF(DD,'2012-07-21 00:00:00.000','2012-07-23 00:00:00.000')+1
/*
ID    违规人    违规时间
----------------------------------------------
8    孙明    2012-07-23 00:00:00.000
4    孙明    2012-07-22 00:00:00.000
2    孙明    2012-07-21 00:00:00.000
*/

------解决方案--------------------
SQL code

CREATE TABLE [dbo].[testtable](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[违规人] [varchar](max) NULL,
[违规时间] [datetime] NULL,