SQL查询语句该怎么写，请高手帮忙
表的关系：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
[解决办法]
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
[解决办法]
------------------------------ 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 行受影响)*/
[解决办法]
if object_id('[userinfo]') is not null drop table [userinfo]gocreate table [userinfo] (NAME nvarchar(4),USERID int)insert into [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 '蔺十',10if object_id('[checkinout]') is not null drop table [checkinout]gocreate table [checkinout] (CHECKTIME datetime,USERID int)insert into [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',8select * from [userinfo]select * from [checkinout]SELECT U.userid,U.NAME,[日期]=convert(date,C.checktime),[起始]=MIN(CONVERT(VARCHAR(5),C.checktime,114)),[终止]=MAX(CONVERT(VARCHAR(5),C.checktime,114))FROM checkinout Cleft JOIN [userinfo] U ON C.userid = U.useridGROUP BY U.userid,U.NAME,convert(date,C.checktime)ORDER BY convert(date,C.checktime),U.userid/*userid NAME 日期 起始 终止1 刘一 2011-11-01 08:25 17:332 周二 2011-11-01 08:31 17:424 徐四 2011-11-01 08:26 19:505 王五 2011-11-01 08:25 17:327 黄七 2011-11-01 08:26 17:323 李三 2011-11-02 13:57 17:484 徐四 2011-11-02 08:26 18:515 王五 2011-11-02 08:26 17:306 邹六 2011-11-02 08:27 18:347 黄七 2011-11-02 08:26 19:498 林八 2011-11-02 08:28 08:283 李三 2011-11-03 08:27 18:474 徐四 2011-11-03 08:10 17:367 黄七 2011-11-03 08:28 17:388 林八 2011-11-03 08:24 17:31*/
[解决办法]
------------------------------ 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,CONVERT(varchar(10),CHECKTIME,120) as CHECKTIME,Min(CHECKTIME) as mintime,MAX(CHECKTIME) as maxtime from checkinout group by USERID,CONVERT(varchar(10),CHECKTIME,120) )bon a.USERID=b.USERIDand a.CHECKTIME=b.CHECKTIME----------------结果----------------------------/* ((10 行受影响)(29 行受影响)checktime name mintime maxtime---------- ---- ----------------------- -----------------------2011-11-01 黄七 2011-11-01 08:26:00.000 2011-11-01 17:32:00.0002011-11-01 李三 NULL NULL2011-11-01 林八 NULL NULL2011-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-01 17:32:00.0002011-11-01 徐四 2011-11-01 08:26:00.000 2011-11-01 19:50:00.0002011-11-01 易九 NULL NULL2011-11-01 周二 2011-11-01 08:31:00.000 2011-11-01 17:42:00.0002011-11-01 邹六 NULL NULL2011-11-02 黄七 2011-11-02 08:26:00.000 2011-11-02 19:49:00.0002011-11-02 李三 2011-11-02 13:57:00.000 2011-11-02 17:48:00.0002011-11-02 林八 2011-11-02 08:28:00.000 2011-11-02 08:28:00.0002011-11-02 蔺十 NULL NULL2011-11-02 刘一 NULL NULL2011-11-02 王五 2011-11-02 08:26:00.000 2011-11-02 17:30:00.0002011-11-02 徐四 2011-11-02 08:26:00.000 2011-11-02 18:51:00.0002011-11-02 易九 NULL NULL2011-11-02 周二 NULL NULL2011-11-02 邹六 2011-11-02 08:27:00.000 2011-11-02 18:34:00.0002011-11-03 黄七 2011-11-03 08:28:00.000 2011-11-03 17:38:00.0002011-11-03 李三 2011-11-03 08:27:00.000 2011-11-03 18:47:00.0002011-11-03 林八 2011-11-03 08:24:00.000 2011-11-03 17:31:00.0002011-11-03 蔺十 NULL NULL2011-11-03 刘一 NULL NULL2011-11-03 王五 NULL NULL2011-11-03 徐四 2011-11-03 08:10:00.000 2011-11-03 17:36:00.0002011-11-03 易九 NULL NULL2011-11-03 周二 NULL NULL2011-11-03 邹六 NULL NULL(30 行受影响)*/
[解决办法]
------------------------------ 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,CONVERT(varchar(10),CHECKTIME,120) as CHECKTIME,Min(convert(varchar(5),CHECKTIME,108)) as mintime,MAX(convert(varchar(5),CHECKTIME,108)) as maxtime from checkinout group by USERID,CONVERT(varchar(10),CHECKTIME,120) )bon a.USERID=b.USERIDand a.CHECKTIME=b.CHECKTIME----------------结果----------------------------/* (10 行受影响)(29 行受影响)checktime name mintime maxtime---------- ---- ------- -------2011-11-01 黄七 08:26 17:322011-11-01 李三 NULL NULL2011-11-01 林八 NULL NULL2011-11-01 蔺十 NULL NULL2011-11-01 刘一 08:25 17:332011-11-01 王五 08:25 17:322011-11-01 徐四 08:26 19:502011-11-01 易九 NULL NULL2011-11-01 周二 08:31 17:422011-11-01 邹六 NULL NULL2011-11-02 黄七 08:26 19:492011-11-02 李三 13:57 17:482011-11-02 林八 08:28 08:282011-11-02 蔺十 NULL NULL2011-11-02 刘一 NULL NULL2011-11-02 王五 08:26 17:302011-11-02 徐四 08:26 18:512011-11-02 易九 NULL NULL2011-11-02 周二 NULL NULL2011-11-02 邹六 08:27 18:342011-11-03 黄七 08:28 17:382011-11-03 李三 08:27 18:472011-11-03 林八 08:24 17:312011-11-03 蔺十 NULL NULL2011-11-03 刘一 NULL NULL2011-11-03 王五 NULL NULL2011-11-03 徐四 08:10 17:362011-11-03 易九 NULL NULL2011-11-03 周二 NULL NULL2011-11-03 邹六 NULL NULL(30 行受影响)*/
[解决办法]
select a.dt,a.name,max(convert(varchar(5),b.checktime,108))maxtime,min(convert(varchar(5),b.checktime,108))mintimefrom (select distinct convert(varchar(10),a.checktime,120)dt,b.userid,b.name from checkinout a,userinfo b)a left join checkinout b on a.dt=convert(varchar(10),b.checktime,120) and a.userid=b.useridgroup by a.dt,a.nameorder by 1/*dt name maxtime mintime---------- ---------- ------- -------2011-11-01 黄七 17:32 08:262011-11-01 李三 NULL NULL2011-11-01 林八 NULL NULL2011-11-01 蔺十 NULL NULL2011-11-01 刘一 17:33 08:252011-11-01 王五 17:32 08:252011-11-01 徐四 19:50 08:262011-11-01 易九 NULL NULL2011-11-01 周二 17:42 08:312011-11-01 邹六 NULL NULL2011-11-02 黄七 19:49 08:262011-11-02 李三 17:48 13:572011-11-02 林八 08:28 08:282011-11-02 蔺十 NULL NULL2011-11-02 刘一 NULL NULL2011-11-02 王五 17:30 08:262011-11-02 徐四 18:51 08:262011-11-02 易九 NULL NULL2011-11-02 周二 NULL NULL2011-11-02 邹六 18:34 08:272011-11-03 黄七 17:38 08:282011-11-03 李三 18:47 08:272011-11-03 林八 17:31 08:242011-11-03 蔺十 NULL NULL2011-11-03 刘一 NULL NULL2011-11-03 王五 NULL NULL2011-11-03 徐四 17:36 08:102011-11-03 易九 NULL NULL2011-11-03 周二 NULL NULL2011-11-03 邹六 NULL NULL警告: 聚合或其他 SET 操作消除了空值。(30 行受影响)*/
[解决办法]
--谨遵小F要求，前来优化;with cte as(select * from userinfo u,(select distinct convert(varchar(10),checktime,120) mydate from checkinout) t )select a.mydate,a.userid,a.name,mintime=convert(varchar(5),min(c.checktime),108),mintime=convert(varchar(5),max(c.checktime),108)from cte a left join checkinout c on (a.userid=c.userid and a.mydate=convert(varchar(10),c.checktime,120))group by a.mydate,a.userid,a.nameorder by 1,2/*mydate userid name mintime mintime---------- ----------- ---- ------- -------2011-11-01 1 刘一 08:25 17:332011-11-01 2 周二 08:31 17:422011-11-01 3 李三 NULL NULL2011-11-01 4 徐四 08:26 19:502011-11-01 5 王五 08:25 17:322011-11-01 6 邹六 NULL NULL2011-11-01 7 黄七 08:26 17:322011-11-01 8 林八 NULL NULL2011-11-01 9 易九 NULL NULL2011-11-01 10 蔺十 NULL NULL2011-11-02 1 刘一 NULL NULL2011-11-02 2 周二 NULL NULL2011-11-02 3 李三 13:57 17:482011-11-02 4 徐四 08:26 18:512011-11-02 5 王五 08:26 17:302011-11-02 6 邹六 08:27 18:342011-11-02 7 黄七 08:26 19:492011-11-02 8 林八 08:28 08:282011-11-02 9 易九 NULL NULL2011-11-02 10 蔺十 NULL NULL2011-11-03 1 刘一 NULL NULL2011-11-03 2 周二 NULL NULL2011-11-03 3 李三 08:27 18:472011-11-03 4 徐四 08:10 17:362011-11-03 5 王五 NULL NULL2011-11-03 6 邹六 NULL NULL2011-11-03 7 黄七 08:28 17:382011-11-03 8 林八 08:24 17:312011-11-03 9 易九 NULL NULL2011-11-03 10 蔺十 NULL NULL警告: 聚合或其他 SET 操作消除了 Null 值。(30 行受影响)
[解决办法]
好快
思路，需求主要是要userid和年月日都显示，不足的补null
所以先cross join构造主表，再left join即可
create table userinfo(name char(8), USERID int)insert into userinfoselect '刘一', 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 '蔺十', 10create table checkinout(CHECKTIME datetime, USERID int)insert into checkinoutselect '2011-11-1 8:25', 1 union all select '2011-11-1 17:33', 1 union all select '2011-11-1 8:31', 2 union all select '2011-11-1 17:42', 2 union all select '2011-11-1 8:26', 4 union all select '2011-11-1 19:50', 4 union all select '2011-11-1 8:25', 5 union all select '2011-11-1 17:32', 5 union all select '2011-11-1 8:26', 7 union all select '2011-11-1 17:32', 7 union all select '2011-11-2 13:57', 3 union all select '2011-11-2 17:48', 3 union all select '2011-11-2 8:26', 4 union all select '2011-11-2 18:51', 4 union all select '2011-11-2 8:26', 5 union all select '2011-11-2 17:30', 5 union all select '2011-11-2 8:27', 6 union all select '2011-11-2 18:34', 6 union all select '2011-11-2 8:26', 7 union all select '2011-11-2 19:49', 7 union all select '2011-11-2 8:28', 8 union all select '2011-11-3 8:27', 3 union all select '2011-11-3 18:47', 3 union all select '2011-11-3 8:10', 4 union all select '2011-11-3 17:36', 4 union all select '2011-11-3 8:28', 7 union all select '2011-11-3 17:38', 7 union all select '2011-11-3 8:24', 8 union all select '2011-11-3 17:31', 8select c.MYDATE,c.name,d.MINTIME,d.MAXTIMEfrom(select * from(select distinct convert(varchar,CHECKTIME,23) 'MYDATE' from checkinout) across join(select * from userinfo) b) cleft join (select convert(varchar,CHECKTIME,23) 'MYDATE',USERID,left(convert(varchar,min(CHECKTIME),108),5) 'MINTIME',left(convert(varchar,max(CHECKTIME),108),5) 'MAXTIME'from checkinout group by convert(varchar,CHECKTIME,23),USERID) don c.MYDATE=d.MYDATE and c.USERID=d.USERID MYDATE name MINTIME MAXTIME-------------- -------- ---------- ----------2011-11-01 刘一 08:25 17:332011-11-01 周二 08:31 17:422011-11-01 李三 NULL NULL2011-11-01 徐四 08:26 19:502011-11-01 王五 08:25 17:322011-11-01 邹六 NULL NULL2011-11-01 黄七 08:26 17:322011-11-01 林八 NULL NULL2011-11-01 易九 NULL NULL2011-11-01 蔺十 NULL NULL2011-11-02 刘一 NULL NULL2011-11-02 周二 NULL NULL2011-11-02 李三 13:57 17:482011-11-02 徐四 08:26 18:512011-11-02 王五 08:26 17:302011-11-02 邹六 08:27 18:342011-11-02 黄七 08:26 19:492011-11-02 林八 08:28 08:282011-11-02 易九 NULL NULL2011-11-02 蔺十 NULL NULL2011-11-03 刘一 NULL NULL2011-11-03 周二 NULL NULL2011-11-03 李三 08:27 18:472011-11-03 徐四 08:10 17:362011-11-03 王五 NULL NULL2011-11-03 邹六 NULL NULL2011-11-03 黄七 08:28 17:382011-11-03 林八 08:24 17:312011-11-03 易九 NULL NULL2011-11-03 蔺十 NULL NULL(30 row(s) affected)