联合查询

[XinJW]

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) order by WorkID,atd_datetime DESC

得出如下结果

atd_datetime Type workid
2012-03-10 08:38:00.000 上班 1111
2012-03-09 18:21:00.000 下班 1111
2012-03-09 07:56:00.000 上班 2222
2012-03-08 22:08:00.000 下班 2222
2012-03-08 07:55:00.000 上班 1111
2012-03-06 15:04:00.000 下班 1111

还有表Employee
Employee_Name SN
王静 1111
李明柯 2222

如何得出下面结果
atd_datetime Type workid Remark Name
2012-03-10 08:38:00.000 上班 1111 迟到 王静
2012-03-09 18:21:00.000 下班 1111 正常 王静
2012-03-09 07:56:00.000 上班 2222 正常 李明柯
2012-03-08 22:08:00.000 下班 2222 正常 李明柯
2012-03-08 07:55:00.000 上班 1111 正常 王静
2012-03-06 15:04:00.000 下班 1111 早退 王静

[XinJW]

SELECT WorkID, MIN(atd_datetime) atd_datetime, '上班' type, CASE WHEN datepart(hh, 
      MIN(atd_datetime)) > 8 THEN '迟到' ELSE '正常' END AS remark, 
      Employee_Name
FROM (SELECT workid, CONVERT(datetime, atd_datetime) atd_datetime
        FROM Attendance ) A JOIN
      Employee aa ON workid = aa.sn
GROUP BY WorkID, CONVERT(varchar, atd_datetime, 23), Employee_Name
UNION ALL
SELECT WorkID, MAX(atd_datetime) atd_datetime, '下班' type, CASE WHEN datepart(hh, 
      Max(atd_datetime)) <17 THEN '迟到' ELSE '正常' END AS remark, Employee_Name
FROM (SELECT workid, CONVERT(datetime, atd_datetime) atd_datetime
        FROM Attendance ) A JOIN
      Employee aa ON workid = aa.sn
GROUP BY WorkID, CONVERT(varchar, atd_datetime, 23), Employee_Name
ORDER BY WorkID, atd_datetime DESC

这样居然可以了。

[学习中-------]

上班时间8点到5点半？

[XinJW]

是的。上班时间上午8：00上班到下午5：30下班。

[学习中-------]

select WorkID,MIN(atd_datetime)atd_datetime,'上班'type,case when datepart(hh,MIN(atd_datetime))> 8 then "迟到" else “正常" as remark,Employee_Name from (
select workid,CONVERT(datetime,atd_datetime)atd_datetime from Attendance
)A join Employee aa on workid=aa.sn
group by WorkID,CONVERT(varchar,atd_datetime,23),remark,Employee_Name
union all
select WorkID,max(atd_datetime)atd_datetime,'下班'type,type,case when datepart(hh,Max(atd_datetime))>16 then "正常" else "早退" as remark ,Employee_Name from (
select workid,CONVERT(datetime,atd_datetime)atd_datetime from Attendance
)A join Employee aa on workid=aa.sn
group by WorkID,CONVERT(varchar,atd_datetime,23),remark,Employee_Name order by WorkID,atd_datetime DESC

[XinJW]

服务器: 消息 156，级别 15，状态 1，行 1 在关键字 'as' 附近有语法错误。 服务器: 消息 170，级别 15，状态 1，行 3 第 3 行: 'A' 附近有语法错误。 服务器: 消息 156，级别 15，状态 1，行 6 在关键字 'as' 附近有语法错误。 服务器: 消息 170，级别 15，状态 1，行 8 第 8 行: 'A' 附近有语法错误。

[学习中-------]

引用 4 楼 XinJW 的回复:

服务器: 消息 156，级别 15，状态 1，行 1 在关键字 'as' 附近有语法错误。 服务器: 消息 170，级别 15，状态 1，行 3 第 3 行: 'A' 附近有语法错误。 服务器: 消息 156，级别 15，状态 1，行 6 在关键字 'as' 附近有语法错误。 服务器: 消息 170，级别 15，状态 1，行 8 第 8 行: 'A' 附近有语法错误。

select WorkID,MIN(atd_datetime)atd_datetime,'上班'type,case when datepart(hh,MIN(atd_datetime))> 8 then '迟到' else '正常'  end as remark ,Employee_Name from (
select workid,CONVERT(datetime,atd_datetime)atd_datetime from Attendance
)A join Employee aa on workid=aa.sn
group by WorkID,CONVERT(varchar,atd_datetime,23),remark,Employee_Name
union all
select WorkID,max(atd_datetime)atd_datetime,'下班'type,type,case when (datepart(hh,Max(atd_datetime))>17 and datepart(mi,Max(atd_datetime))>30) then '正常' else '早退' end as remark ,Employee_Name from (
select workid,CONVERT(datetime,atd_datetime)atd_datetime from Attendance
)A join Employee aa on workid=aa.sn
group by WorkID,CONVERT(varchar,atd_datetime,23),remark,Employee_Name order by WorkID,at

[学习中-------]

引用 5 楼 myselfff 的回复:

[quote=引用 4 楼 XinJW 的回复:] 服务器: 消息 156，级别 15，状态 1，行 1 在关键字 'as' 附近有语法错误。 服务器: 消息 170，级别 15，状态 1，行 3 第 3 行: 'A' 附近有语法错误。 服务器: 消息 156，级别 15，状态 1，行 6 在关键字 'as' 附近有语法错误。 服务器: 消息 170，级别 15，状态 1，行 8 第 8 行: 'A' 附近有语法错误。

select WorkID,MIN(atd_datetime)atd_datetime,'上班'type,case when datepart(hh,MIN(atd_datetime))> 8 then '迟到' else '正常'  end as remark ,Employee_Name from (
select workid,CONVERT(datetime,atd_datetime)atd_datetime from Attendance
)A join Employee aa on workid=aa.sn
group by WorkID,CONVERT(varchar,atd_datetime,23),remark,Employee_Name
union all
select WorkID,max(atd_datetime)atd_datetime,'下班'type,type,case when (datepart(hh,Max(atd_datetime))>17 and datepart(mi,Max(atd_datetime))>30) then '正常' else '早退' end as remark ,Employee_Name from (
select workid,CONVERT(datetime,atd_datetime)atd_datetime from Attendance
)A join Employee aa on workid=aa.sn
group by WorkID,CONVERT(varchar,atd_datetime,23),remark,Employee_Name order by WorkID,at

[/quote] select WorkID,MIN(atd_datetime)atd_datetime,'上班'type,case when datepart(hh,MIN(atd_datetime))> 8 then '迟到' else '正常' end as remark ,Employee_Name from ( select workid,CONVERT(datetime,atd_datetime)atd_datetime from Attendance )A join Employee aa on workid=aa.sn group by WorkID,CONVERT(varchar,atd_datetime,23),remark,Employee_Name union all select WorkID,max(atd_datetime)atd_datetime,'下班'type,type,case when (datepart(hh,Max(atd_datetime))>17 and datepart(mi,Max(atd_datetime))>30) then '正常' else '早退' end as remark ,Employee_Name from ( select workid,CONVERT(datetime,atd_datetime)atd_datetime from Attendance )A join Employee aa on workid=aa.sn group by WorkID,CONVERT(varchar,atd_datetime,23),remark,Employee_Name order by WorkID,atd_datetime DESC

[XinJW]

还是有问题。 服务器: 消息 207，级别 16，状态 3，行 1 列名 'remark' 无效。 服务器: 消息 207，级别 16，状态 1，行 1 列名 'remark' 无效。 服务器: 消息 207，级别 16，状态 1，行 1 列名 'at' 无效。

[XinJW]

还有第二段CASE WHEN 前面有两个TYPE是不是多了一个？