这是我的表架构
id | name | number | status | logtime | logdate
1 John 1001 in 8:00 AM 10/01/2016
2 John 1001 out 5:00 PM 10/01/2016
3 Carl 1002 in 8:01 AM 10/01/2016
4 John 1001 in 8:00 AM 10/02/2016
5 John 1001 out 5:00 PM 10/02/2016
以及如何将同一日志日期的两行合并为一，结果如下
name | number | time_in | time_out | logdate
John 1001 8:00 AM 5:00 PM 10/01/2016
John 1001 8:00 AM 5:00 PM 10/02/2016
Carl 1002 8:01 AM NULL 10/01/2016
我在查询中使用了CASE语法，但结果与想要的结果不同。当然，我的查询有问题。这是我的查询顺便说一句：
SELECT
number,
name,
CASE status WHEN 'in' THEN logtime END AS time_in,
CASE status WHEN 'out' THEN logtime END AS time_out,
LogDate
FROM
tbl_attendanceraw
Group by logdate
TIA！
您需要条件聚合：
SELECT number, name,
MAX(CASE status WHEN 'in' THEN logtime END) AS time_in,
MAX(CASE status WHEN 'out' THEN logtime END) AS time_out,
LogDate
FROM tbl_attendanceraw
Group by logdate, number, name;
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句