怎么去取每天都有的值

如何去取每天都有的值~
CREATE TABLE [dbo].[testtable](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[违规人] [varchar](max) NULL,
[违规时间] [datetime] NULL,
 CONSTRAINT [PK_testtable] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[testtable] ON
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))
SET IDENTITY_INSERT [dbo].[testtable] OFF


如题我如何 查询出 时间段内每天都有的违规人

[解决办法]

SQL code

declare @datestart datetime,@dateend datetimeselect @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)GOSET ANSI_PADDING OFFGOINSERT [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 tas(select     *,    px=COUNT([违规人])over(partition by [违规人])from     [testtable] awhere     exists(        select             1         from             [testtable] b         where             a.[违规时间]<>b.[违规时间]             and a.[违规人]=b.[违规人]    ))select     [ID],    [违规人],    [违规时间]from    twhere     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.0004    孙明    2012-07-22 00:00:00.0002    孙明    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, CONSTRAINT [PK_testtable] PRIMARY KEY CLUSTERED  ([ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]SET ANSI_PADDING OFFSET IDENTITY_INSERT [dbo].[testtable] ONINSERT [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))SET IDENTITY_INSERT [dbo].[testtable] OFFselect [违规人]from(select [违规人],       convert(varchar(10),[违规时间],120) 违规时间from [testtable]group by [违规人],convert(varchar(10),[违规时间],120)) agroup by [违规人]having count(1)=(select count(distinct convert(varchar(10),[违规时间],120)) from [testtable])/*违规人-------------------孙明  (1 row(s) affected)*/ 
 
[解决办法]
SQL code
--2005;with t as(select 违规人,convert(varchar(10),违规时间,120)as 违规时间 from [testtable] group by 违规人,convert(varchar(10),违规时间,120)) select 违规人 from t group by 违规人having count(*)=(select count(distinct 违规时间) from t)