日期:2014-05-18 浏览次数:20646 次
select convert(varchar,a.CHECKTIME,23) 'MYDATE', b.name, min(CHECKTIME) 'MINTIME', max(CHECKTIME) 'MAXTIME' from checkinout a inner join userinfo b on a.USERID=b.USERID group 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 all select '周二',2 union all select '李三',3 union all select '徐四',4 union all select '王五',5 union all select '邹六',6 union all select '黄七',7 union all select '林八',8 union all select '易九',9 union all select '蔺十',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 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