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