如何去取每天都有的值~

[sunming8915]

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


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

[人生无悔]

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
			);

[开着拖拉机泡妞]

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
*/

[唐诗三百首]

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 OFF

SET IDENTITY_INSERT [dbo].[testtable] ON
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


select [违规人]
from
(select [违规人],
       convert(varchar(10),[违规时间],120) 违规时间
from [testtable]
group by [违规人],convert(varchar(10),[违规时间],120)
) a
group by [违规人]
having count(1)=
(select count(distinct convert(varchar(10),[违规时间],120))
 from [testtable])

/*
违规人
-------------------
孙明  

(1 row(s) affected)
*/

[筱筱澄]

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