从表格中查找每周摘要

发布于 2024-09-06 13:32:44 字数 132 浏览 8 评论 0原文

我在这里编写一条 SQL 语句，用于从表中查找每周摘要。我有一个包含以下字段的表：

UIN, Date, Staff, work_hours

现在我想收集员工一周工作多少小时的信息。

原文

I am here to write a SQL statement for finding the weekly summary from a table. I had a table with following fields:

UIN, Date, Staff, work_hours

Now I would like to gather information how many hours has a staff worked in one week.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

野稚 2024-09-13 13:32:44

从您的问题中很难看出，但如果您正在查找每位员工一周内的总工作时间，请尝试以下操作：

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)

It's hard to tell from your question, but if you're looking for the total hours in one week by each employee, try the following:

SELECT Staff, SUM(work_hours)
FROM YourTable
WHERE Date BETWEEN week_start_day AND week_end_day
GROUP BY Staff

Otherwise, if you're looking for a full report, summarized by week, you can try the following, which will give you each staff's weekly hours, aggregated by week:

SELECT YEAR(Date), WEEK(Date), Staff, SUM(work_hours)
FROM YourTable
GROUP BY YEAR(Date), WEEK(Date), Staff
ORDER BY YEAR(Date), WEEK(Date)

回复收藏 0 原文

国产ˉ祖宗 2024-09-13 13:32:44

您可能想尝试如下所示：

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)

You may want to try something like the following:

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;

Test case (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);

Result:

+-------+---------+----------------+
| 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)

回复收藏 0 原文

~没有更多了~