----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-05-21 19:51:43
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[kaoqin]
if object_id('[kaoqin]') is not null drop table [kaoqin]
go
create table [kaoqin]([id] int,[userid] int,[userName] nvarchar(2),[cardid] int,[eventTime] datetime)
insert [kaoqin]
select 1,1,'A',1,'2014-01-02 08:00:00' union all
select 2,1,'A',1,'2014-01-02 18:00:00' union all
select 3,1,'A',1,'2014-01-02 19:00:00' union all
select 4,2,'B',2,'2014-01-02 10:00:00' union all
select 5,1,'A',1,'2014-01-03 8:00:00' union all
select 6,1,'A',1,'2014-01-03 18:50:00'
--------------生成数据--------------------------
DECLARE @startTime DATETIME
DECLARE @endtime DATETIMESET @startTime='2014-01-01'
SET @endtime='2014-05-21'
SELECT a.id ,
b.userid ,
b.username ,
b.cardid ,
a.[eventTime] startTime ,
c.[eventTime] AS endTime
FROM [kaoqin] a
INNER JOIN ( SELECT [userid] ,
username ,
cardid ,
CONVERT(VARCHAR(10), [eventTime], 23) [eventTime] ,
MIN([eventTime]) startTime ,
MAX([eventTime]) endTime
FROM [kaoqin]
WHERE [eventTime] BETWEEN @startTime AND @endtime
GROUP BY [userid] ,
username ,
cardid ,
CONVERT(VARCHAR(10), [eventTime], 23)
) b ON a.userid = b.userid
AND a.cardid = b.cardid
AND a.[eventTime] = b.startTime
INNER JOIN [kaoqin] c ON c.userid = b.userid
AND c.cardid = b.cardid
AND c.[eventTime] = b.endTime
----------------结果----------------------------
/*
id userid username cardid startTime endTime
----------- ----------- -------- ----------- ----------------------- -----------------------
1 1 A 1 2014-01-02 08:00:00.000 2014-01-02 19:00:00.000
4 2 B 2 2014-01-02 10:00:00.000 2014-01-02 10:00:00.000
5 1 A 1 2014-01-03 08:00:00.000 2014-01-03 18:50:00.000*/
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-05-21 19:51:43
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[kaoqin]
if object_id('[kaoqin]') is not null drop table [kaoqin]
go
create table [kaoqin]([id] int,[userid] int,[userName] nvarchar(2),[cardid] int,[eventTime] datetime)
insert [kaoqin]
select 1,1,'A',1,'2014-01-02 08:00:00' union all
select 2,1,'A',1,'2014-01-02 18:00:00' union all
select 3,1,'A',1,'2014-01-02 19:00:00' union all
select 4,2,'B',2,'2014-01-02 10:00:00' union all
select 5,1,'A',1,'2014-01-03 8:00:00' union all
select 6,1,'A',1,'2014-01-03 18:50:00'
--------------生成数据--------------------------
DECLARE @startTime DATETIME
DECLARE @endtime DATETIMESET @startTime='2014-01-01'
SET @endtime='2014-05-21'
SELECT a.id ,
b.userid ,
b.username ,
b.cardid ,
a.[eventTime] startTime ,
c.[eventTime] AS endTime
FROM [kaoqin] a
INNER JOIN ( SELECT [userid] ,
username ,
cardid ,
CONVERT(VARCHAR(10), [eventTime], 23) [eventTime] ,
MIN([eventTime]) startTime ,
MAX([eventTime]) endTime
FROM [kaoqin]
WHERE [eventTime] BETWEEN @startTime AND @endtime
GROUP BY [userid] ,
username ,
cardid ,
CONVERT(VARCHAR(10), [eventTime], 23)
) b ON a.userid = b.userid
AND a.cardid = b.cardid
AND a.[eventTime] = b.startTime
INNER JOIN [kaoqin] c ON c.userid = b.userid
AND c.cardid = b.cardid
AND c.[eventTime] = b.endTime
----------------结果----------------------------
/*
id userid username cardid startTime endTime
----------- ----------- -------- ----------- ----------------------- -----------------------
1 1 A 1 2014-01-02 08:00:00.000 2014-01-02 19:00:00.000
4 2 B 2 2014-01-02 10:00:00.000 2014-01-02 10:00:00.000
5 1 A 1 2014-01-03 08:00:00.000 2014-01-03 18:50:00.000*/
1、userid一般就能决定username,所以join的时候我只join了userid,如果userid不能唯一决定username,可以在on中加上username
2、直接出和你期待结果一样的结果,其实不用那么复杂的写法,但是我觉得不严谨,或者仅仅是巧合,所以我觉得我的逻辑还是必要的,只是不保证有没有更简洁的写法