我在这里编写了一条SQL语句,用于从表中查找每周摘要。我有一个包含以下字段的表:
UIN, Date, Staff, work_hours现在,我想收集一名员工在一周内工作了多少小时的信息。
发布于 2010-06-21 15:01:42
从你的问题中很难看出,但如果你正在寻找每个员工在一周内的总工作时间,请尝试以下方法:
SELECT Staff, SUM(work_hours)
FROM YourTable
WHERE Date BETWEEN week_start_day AND week_end_day
GROUP BY Staff否则,如果您正在寻找按周汇总的完整报告,您可以尝试以下方法,这将为您提供每个员工每周的工作时间,按周汇总:
SELECT YEAR(Date), WEEK(Date), Staff, SUM(work_hours)
FROM YourTable
GROUP BY YEAR(Date), WEEK(Date), Staff
ORDER BY YEAR(Date), WEEK(Date)发布于 2010-06-21 15:02:59
您可能想要尝试如下所示:
SELECT staff,
WEEK(date) week_no,
SUM(work_hours) as work_hours_sum
FROM log
GROUP BY staff, WEEK(date)
ORDER BY WEEK(date), staff;测试用例(MySQL):
CREATE TABLE log (
uin int auto_increment primary key,
date date,
staff int,
work_hours int
);
INSERT INTO log (date, staff, work_hours) VALUES ('2010-06-01', 1, 5);
INSERT INTO log (date, staff, work_hours) VALUES ('2010-06-01', 2, 7);
INSERT INTO log (date, staff, work_hours) VALUES ('2010-06-02', 1, 2);
INSERT INTO log (date, staff, work_hours) VALUES ('2010-06-02', 2, 1);
INSERT INTO log (date, staff, work_hours) VALUES ('2010-06-08', 1, 2);
INSERT INTO log (date, staff, work_hours) VALUES ('2010-06-08', 2, 5);
INSERT INTO log (date, staff, work_hours) VALUES ('2010-06-09', 1, 6);
INSERT INTO log (date, staff, work_hours) VALUES ('2010-06-09', 2, 5);结果:
+-------+---------+----------------+
| staff | week_no | work_hours_sum |
+-------+---------+----------------+
| 1 | 22 | 7 |
| 2 | 22 | 8 |
| 1 | 23 | 8 |
| 2 | 23 | 10 |
+-------+---------+----------------+
4 rows in set (0.00 sec)https://stackoverflow.com/questions/3082631
复制相似问题