怎么取数据库中一个条件下多条记录中的两条数据。
有表Attendance
WorkID Atd_Datetime
111111 2013-06-01 7:55
111111 2013-06-01 7:58
111111 2013-06-01 8:55
111111 2013-06-01 18:08
111111 2013-06-01 20:09
222222 2013-06-01 7:58
222222 2013-06-01 18:22
怎么样得出下面结果
WorkID Atd_Datetime TYPE
111111 2013-06-01 7:55 上班
111111 2013-06-01 20:09 下班
222222 2013-06-01 7:58 上班
222222 2013-06-01 18:22 下班
[解决办法]
with Attendance(WorkID,Atd_Datetime)as(
select 111111,'2013-06-01 7:55' union all
select 111111,'2013-06-01 7:58' union all
select 111111,'2013-06-01 8:55' union all
select 111111,'2013-06-01 18:08' union all
select 111111,'2013-06-01 20:09' union all
select 222222,'2013-06-01 7:58' union all
select 222222,'2013-06-01 18:22')
select * from (
select WorkID,MIN(atd_datetime)atd_datetime,'上班'type from (
select workid,CONVERT(datetime,atd_datetime)atd_datetime from Attendance
)A
group by WorkID,CONVERT(varchar,atd_datetime,23)
union all
select WorkID,max(atd_datetime)atd_datetime,'下班'type from (
select workid,CONVERT(datetime,atd_datetime)atd_datetime from Attendance
)A
group by WorkID,CONVERT(varchar,atd_datetime,23)
)a order by 1,2
