表的关系：userinfo和checkinout关系如下
userinfo：
NAME USERID
刘一 1
周二 2
李三 3
徐四 4
王五 5
邹六 6
黄七 7
林八 8
易九 9
蔺十 10
checkinout：
CHECKTIME USERID
2011-11-1 8:25 1
2011-11-1 17:33 1
2011-11-1 8:31 2
2011-11-1 17:42 2
2011-11-1 8:26 4
2011-11-1 19:50 4
2011-11-1 8:25 5
2011-11-1 17:32 5
2011-11-1 8:26 7
2011-11-1 17:32 7
2011-11-2 13:57 3
2011-11-2 17:48 3
2011-11-2 8:26 4
2011-11-2 18:51 4
2011-11-2 8:26 5
2011-11-2 17:30 5
2011-11-2 8:27 6
2011-11-2 18:34 6
2011-11-2 8:26 7
2011-11-2 19:49 7
2011-11-2 8:28 8
2011-11-3 8:27 3
2011-11-3 18:47 3
2011-11-3 8:10 4
2011-11-3 17:36 4
2011-11-3 8:28 7
2011-11-3 17:38 7
2011-11-3 8:24 8
2011-11-3 17:31 8
查询结果如下：
MYDATE NAME MINTIME MAXTIME
2011-11-1 刘一 8:25 17:33
2011-11-1 周二 8:31 17:42
2011-11-1 李三 NULL NULL
2011-11-1 徐四 8:26 19:50
2011-11-1 王五 8:25 17:32
2011-11-1 邹六 NULL NULL
2011-11-1 黄七 8:26 17:32
2011-11-1 林八 NULL NULL
2011-11-1 易九 NULL NULL
2011-11-1 蔺十 NULL NULL
2011-11-2 刘一 NULL NULL
2011-11-2 周二 NULL NULL
2011-11-2 李三 13:57 17:48
2011-11-2 徐四 8:26 18:51
2011-11-2 王五 8:26 17:30
2011-11-2 邹六 8:27 18:34
2011-11-2 黄七 8:26 19:49
2011-11-2 林八 8:28 8:28
2011-11-2 易九 NULL NULL
2011-11-2 蔺十 NULL NULL
2011-11-3 刘一 NULL NULL
2011-11-3 周二 NULL NULL
2011-11-3 李三 8:27 18:47
2011-11-3 徐四 8:10 17:36
2011-11-3 王五 NULL NULL
2011-11-3 邹六 NULL NULL
2011-11-3 黄七 8:28 17:38
2011-11-3 林八 8:24 17:31
2011-11-3 易九 NULL NULL
2011-11-3 蔺十 NULL NULL
------解决方案--------------------
- SQL code
select convert(varchar,a.CHECKTIME,23) 'MYDATE',b.name,min(CHECKTIME) 'MINTIME',max(CHECKTIME) 'MAXTIME'from checkinout ainner join userinfo bon a.USERID=b.USERIDgroup by convert(varchar,a.CHECKTIME,23),b.NAME
------解决方案--------------------
- SQL code
------------------------------ Author :fredrickhu(小F，向高手学习)-- Date :2011-12-07 15:17:09-- Version:-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) -- Apr 22 2011 11:57:00 -- Copyright (c) Microsoft Corporation-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)--------------------------------> 测试数据：[userinfo]if object_id('[userinfo]') is not null drop table [userinfo]go create table [userinfo]([NAME] varchar(4),[USERID] int)insert [userinfo]select '刘一',1 union allselect '周二',2 union allselect '李三',3 union allselect '徐四',4 union allselect '王五',5 union allselect '邹六',6 union allselect '黄七',7 union allselect '林八',8 union allselect '易九',9 union allselect '蔺十',10--> 测试数据：[checkinout]if object_id('[checkinout]') is not null drop table [checkinout]go create table [checkinout]([CHECKTIME] datetime,[USERID] int)insert [checkinout]select '2011-11-1 8:25',1 union allselect '2011-11-1 17:33',1 union allselect '2011-11-1 8:31',2 union allselect '2011-11-1 17:42',2 union allselect '2011-11-1 8:26',4 union allselect '2011-11-1 19:50',4 union allselect '2011-11-1 8:25',5 union allselect '2011-11-1 17:32',5 union allselect '2011-11-1 8:26',7 union allselect '2011-11-1 17:32',7 union allselect '2011-11-2 13:57',3 union allselect '2011-11-2 17:48',3 union allselect '2011-11-2 8:26',4 union allselect '2011-11-2 18:51',4 union allselect '2011-11-2 8:26',5 union allselect '2011-11-2 17:30',5 union allselect '2011-11-2 8:27',6 union allselect '2011-11-2 18:34',6 union allselect '2011-11-2 8:26',7 union allselect '2011-11-2 19:49',7 union allselect '2011-11-2 8:28',8 union allselect '2011-11-3 8:27',3 union allselect '2011-11-3 18:47',3 union allselect '2011-11-3 8:10',4 union allselect '2011-11-3 17:36',4 union allselect '2011-11-3 8:28',7 union allselect '2011-11-3 17:38',7 union allselect '2011-11-3 8:24',8 union allselect '2011-11-3 17:31',8--------------开始查询--------------------------select distinct a.checktime,a.name,b.mintime,b.maxtimefrom(select * from (select CONVERT(varchar(10),CHECKTIME,120) as CHECKTIME from checkinout)a cross join (select NAME,USERID from userinfo)b)a left join(select USERID,Min(CHECKTIME) as mintime,MAX(CHECKTIME) as maxtime from checkinout group by USERID)bon a.USERID=b.USERID----------------结果----------------------------/* (10 行受影响)(29 行受影响)checktime name mintime maxtime---------- ---- ----------------------- -----------------------2011-11-01 黄七 2011-11-01 08:26:00.000 2011-11-03 17:38:00.0002011-11-01 李三 2011-11-02 13:57:00.000 2011-11-03 18:47:00.0002011-11-01 林八 2011-11-02 08:28:00.000 2011-11-03 17:31:00.0002011-11-01 蔺十 NULL NULL2011-11-01 刘一 2011-11-01 08:25:00.000 2011-11-01 17:33:00.0002011-11-01 王五 2011-11-01 08:25:00.000 2011-11-02 17:30:00.0002011-11-01 徐四 2011-11-01 08:26:00.000 2011-11-03 17:36:00.0002011-11-01 易九 NULL NULL2011-11-01 周二 2011-11-01 08:31:00.000 2011-11-01 17:42:00.0002011-11-01 邹六 2011-11-02 08:27:00.000 2011-11-02 18:34:00.0002011-11-02 黄七 2011-11-01 08:26:00.000 2011-11-03 17:38:00.0002011-11-02 李三 2011-11-02 13:57:00.000 2011-11-03 18:47:00.0002011-11-02 林八 2011-11-02 08:28:00.000 2011-11-03 17:31:00.0002011-11-02 蔺十 NULL NULL2011-11-02 刘一 2011-11-01 08:25:00.000 2011-11-01 17:33:00.0002011-11-02 王五 2011-11-01 08:25:00.000 2011-11-02 17:30:00.0002011-11-02 徐四 2011-11-01 08:26:00.000 2011-11-03 17:36:00.0002011-11-02 易九 NULL NULL2011-11-02 周二 2011-11-01 08:31:00.000 2011-11-01 17:42:00.0002011-11-02 邹六 2011-11-02 08:27:00.000 2011-11-02 18:34:00.0002011-11-03 黄七 2011-11-01 08:26:00.000 2011-11-03 17:38:00.0002011-11-03 李三 2011-11-02 13:57:00.000 2011-11-03 18:47:00.0002011-11-03 林八 2011-11-02 08:28:00.000 2011-11-03 17:31:00.0002011-11-03 蔺十 NULL NULL2011-11-03 刘一 2011-11-01 08:25:00.000 2011-11-01 17:33:00.0002011-11-03 王五 2011-11-01 08:25:00.000 2011-11-02 17:30:00.0002011-11-03 徐四 2011-11-01 08:26:00.000 2011-11-03 17:36:00.0002011-11-03 易九 NULL NULL2011-11-03 周二 2011-11-01 08:31:00.000 2011-11-01 17:42:00.0002011-11-03 邹六 2011-11-02 08:27:00.000 2011-11-02 18:34:00.000(30 行受影响)*/