博客内容涉及使用SQL查询从`tbl_time_clock`表中按月份和用户ID获取每周工作时间的总和。查询分别计算了周一到周日的工作秒数，并呈现为每月持续时间矩阵。示例数据包括用户的工作开始和结束时间。
我正在尝试编写一个查询，它会显示工作小时的每月持续时间矩阵。我正试图做到这一点。
我现在有这个。
正确的输出应该是
我的SQL是
`SELECT WEEK(start) as weekId,
(
SELECT SUM(TIME_TO_SEC(TIMEDIFF(stop, start)))
FROM tbl_time_clock
WHERE WEEKDAY(start)=0
) AS Monday,
(
SELECT SUM(TIME_TO_SEC(TIMEDIFF(stop, start)))
FROM tbl_time_clock
WHERE WEEKDAY(start)=1
) AS Tuesday,
(
SELECT SUM(TIME_TO_SEC(TIMEDIFF(stop, start)))
FROM tbl_time_clock
WHERE WEEKDAY(start)=2
) AS Wednesday,
(
SELECT SUM(TIME_TO_SEC(TIMEDIFF(stop, start)))
FROM tbl_time_clock
WHERE WEEKDAY(start)=3
) AS Thursday,
(
SELECT SUM(TIME_TO_SEC(TIMEDIFF(stop, start)))
FROM tbl_time_clock
WHERE WEEKDAY(start)=4
) AS Friday,
(
SELECT SUM(TIME_TO_SEC(TIMEDIFF(stop, start)))
FROM tbl_time_clock
WHERE WEEKDAY(start)=5
) AS Saturday,
(
SELECT SUM(TIME_TO_SEC(TIMEDIFF(stop, start)))
FROM tbl_time_clock
WHERE WEEKDAY(start)=6
) AS Sunday
FROM tbl_time_clock
WHERE MONTH(start) = {$monthId} AND user_id = {$userId}
GROUP BY WEEK(start)`表看起来像
╔═════════╦═════════════════════╦═════════════════════╗
║ user_id ║ start ║ stop ║
╠═════════╬═════════════════════╬═════════════════════╣
║ 1 ║ 2012-08-28 08:14:49 ║ 2012-08-28 10:14:49 ║
║ 1 ║ 2012-08-25 10:00:32 ║ 2012-08-25 16:21:57 ║
╚═════════╩═════════════════════╩═════════════════════╝谢谢！！！
扫一扫
1820
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
