【求助】求一Sql语句，求出给定时间内最大最小值

[xupeihuagudulei]

有一张考勤数据表kaoqin 
id  userid userName cardid eventTime
1    1           A              1        2014-01-02 08:00:00
2    1           A               1       2014-01-02   18:00:00
3    1           A               1       2014-01-02   19:00:00
4    2           B               2       2014-01-02   10:00:00
5    1           A               1       2014-01-03   8:00:00
6    1           A               1       2014-01-03  18:50:00
....................................

传入参数时间段，如:2014-01-01 - 2014-05-21 ,得到每一天（0点到23点）最早刷卡、最晚刷卡记录
id   userid userName cardid           startTime                            endTime
1    1           A              1              2014-01-02 08:00:00             2014-01-02   19:00:00
4    2           B               2             2014-01-02   10:00:00           2014-01-02   10:00:00
5    1           A               1             2014-01-03   8:00:00             2014-01-03  18:50:00
...........................

[發糞塗牆]

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

SET @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、直接出和你期待结果一样的结果，其实不用那么复杂的写法，但是我觉得不严谨，或者仅仅是巧合，所以我觉得我的逻辑还是必要的，只是不保证有没有更简洁的写法