最近园子里有很多介绍SQL语句的文章,小弟一时也耐不住寂寞,也把前几天写的一条关于分组查询的代码分享出来。
前段时间公司让做了一个门禁的项目,在做数据查询这一块的时候,有一个需求,就是作为某个公司的考勤的设备。这样的话,我就得把某个人的刷卡上下班的时间记录(可以通过最早进公司时间和最晚出公司时间来判断)显示出来。然后做这个查询的时候,开始没想那么多,直接在存储过程里面拼SQL脚本,虽然做出来了,我相信数据量大的话,系统肯定非常非常慢。然后最后想到了分组求最大值最小值查询的方法,一条sql语句就能实现,非常简单。
环境
create
table
t2 (UserName
varchar
(
50
),dealTm
datetime
)
insert into t2 values ( ' 1 ' , ' 2011-03-07 08:20:10 ' )
insert into t2 values ( ' 1 ' , ' 2011-03-07 15:20:10 ' )
insert into t2 values ( ' 1 ' , ' 2011-03-07 15:30:10 ' )
insert into t2 values ( ' 1 ' , ' 2011-03-07 17:35:10 ' )
insert into t2 values ( ' 1 ' , ' 2011-03-08 08:20:10 ' )
insert into t2 values ( ' 1 ' , ' 2011-03-08 17:31:10 ' )
insert into t2 values ( ' 2 ' , ' 2011-03-08 08:20:10 ' )
insert into t2 values ( ' 2 ' , ' 2011-03-08 17:35:10 ' )
insert into t2 values ( ' 2 ' , ' 2011-03-07 08:20:10 ' )
insert into t2 values ( ' 2 ' , ' 2011-03-07 17:38:10 ' )
insert into t2 values ( ' 3 ' , ' 2011-03-07 08:20:10 ' )
insert into t2 values ( ' 3 ' , ' 2011-03-07 09:20:10 ' )
insert into t2 values ( ' 3 ' , ' 2011-03-07 15:20:10 ' )
insert into t2 values ( ' 3 ' , ' 2011-03-07 17:20:10 ' )
insert into t2 values ( ' 3 ' , ' 2011-03-08 08:20:10 ' )
insert into t2 values ( ' 3 ' , ' 2011-03-08 12:20:10 ' )
insert into t2 values ( ' 3 ' , ' 2011-03-08 14:20:10 ' )
insert into t2 values ( ' 3 ' , ' 2011-03-08 18:20:10 ' )
insert into t2 values ( ' 1 ' , ' 2011-03-07 08:20:10 ' )
insert into t2 values ( ' 1 ' , ' 2011-03-07 15:20:10 ' )
insert into t2 values ( ' 1 ' , ' 2011-03-07 15:30:10 ' )
insert into t2 values ( ' 1 ' , ' 2011-03-07 17:35:10 ' )
insert into t2 values ( ' 1 ' , ' 2011-03-08 08:20:10 ' )
insert into t2 values ( ' 1 ' , ' 2011-03-08 17:31:10 ' )
insert into t2 values ( ' 2 ' , ' 2011-03-08 08:20:10 ' )
insert into t2 values ( ' 2 ' , ' 2011-03-08 17:35:10 ' )
insert into t2 values ( ' 2 ' , ' 2011-03-07 08:20:10 ' )
insert into t2 values ( ' 2 ' , ' 2011-03-07 17:38:10 ' )
insert into t2 values ( ' 3 ' , ' 2011-03-07 08:20:10 ' )
insert into t2 values ( ' 3 ' , ' 2011-03-07 09:20:10 ' )
insert into t2 values ( ' 3 ' , ' 2011-03-07 15:20:10 ' )
insert into t2 values ( ' 3 ' , ' 2011-03-07 17:20:10 ' )
insert into t2 values ( ' 3 ' , ' 2011-03-08 08:20:10 ' )
insert into t2 values ( ' 3 ' , ' 2011-03-08 12:20:10 ' )
insert into t2 values ( ' 3 ' , ' 2011-03-08 14:20:10 ' )
insert into t2 values ( ' 3 ' , ' 2011-03-08 18:20:10 ' )
数据表中内容为
查询语句为
select
UserName, -- 持卡人姓名
Convert ( char ( 8 ),dealTm, 112 ) as date, -- 日期
min (dealTm) as earliestTime , -- 最早时间
max (dealTm) as latestTime -- 最晚时间
from t2
group by Convert ( char ( 8 ),dealTm, 112 ) ,UserName
UserName, -- 持卡人姓名
Convert ( char ( 8 ),dealTm, 112 ) as date, -- 日期
min (dealTm) as earliestTime , -- 最早时间
max (dealTm) as latestTime -- 最晚时间
from t2
group by Convert ( char ( 8 ),dealTm, 112 ) ,UserName
查询结果为: