英文:
Select min of time that got group by status and need more than 1 values
问题
我想找到轮班工人的最短工作时间，但有些日子工人有两次轮班。
表 t：
| ID | date | time | status |
| -------- | ---------- | -------- | -------- |
| 1 | 2022-01-01 | 08:00:00 | Shift In |
| 1 | 2022-01-01 | 08:15:00 | Shift In |
| 1 | 2022-01-01 | 10:30:00 | Shift Out|
| 1 | 2022-01-01 | 12:15:00 | Shift In |
| 1 | 2022-01-01 | 12:18:00 | Shift In |
| 1 | 2022-01-01 | 14:52:00 | Shift Out|
| 1 | 2022-01-01 | 15:00:00 | Shift Out|
| 2 | 2022-01-01 | 17:15:00 | Shift In |
| 2 | 2022-01-01 | 18:15:00 | Shift Out|
| 2 | 2022-01-01 | 18:18:00 | Shift Out|
我需要的输出：
| ID | date | time | status |
| -------- | ---------- | -------- | -------- |
| 1 | 2022-01-01 | 08:00:00 | Shift In |
| 1 | 2022-01-01 | 10:30:00 | Shift Out|
| 1 | 2022-01-01 | 12:15:00 | Shift In |
| 1 | 2022-01-01 | 14:52:00 | Shift Out|
| 2 | 2022-01-01 | 17:15:00 | Shift In |
| 2 | 2022-01-01 | 18:15:00 | Shift Out|
我需要每次轮班的最短时间。当我尝试输出时，总是只显示每位员工的第一个，所以我不知道该怎么做。
我试图找到SQL或PL/SQL返回在同一天工作多次的轮班工人的最短时间输出的解决方案，但我还是不知道该怎么做。
英文:
I want to find min time of shift worker but some days worker have 2 shifts.
Table t:
| ID | date | time | status |
| -------- | ---------- | -------- | -------- |
| 1 | 2022-01-01 | 08:00:00 | Shift In |
| 1 | 2022-01-01 | 08:15:00 | Shift In |
| 1 | 2022-01-01 | 10:30:00 | Shift Out|
| 1 | 2022-01-01 | 12:15:00 | Shift In |
| 1 | 2022-01-01 | 12:18:00 | Shift In |
| 1 | 2022-01-01 | 14:52:00 | Shift Out|
| 1 | 2022-01-01 | 15:00:00 | Shift Out|
| 2 | 2022-01-01 | 17:15:00 | Shift In |
| 2 | 2022-01-01 | 18:15:00 | Shift Out|
| 2 | 2022-01-01 | 18:18:00 | Shift Out|
Output I need:
| ID | date | time | status |
| -------- | ---------- | -------- | -------- |
| 1 | 2022-01-01 | 08:00:00 | Shift In |
| 1 | 2022-01-01 | 10:30:00 | Shift Out|
| 1 | 2022-01-01 | 12:15:00 | Shift In |
| 1 | 2022-01-01 | 14:52:00 | Shift Out|
| 2 | 2022-01-01 | 17:15:00 | Shift In |
| 2 | 2022-01-01 | 18:15:00 | Shift Out|
I need min of time for each shift. When I try output always shows 1 of each employee so I have no idea.
I try to find solutions for return output of min time of shift worker that work more than 1 time in a day on SQL or PL/SQL but I still no idea.
答案1
得分: 2
看起来你只想列出所有的Shift In和Shift Out行，但有一些冗余的行，比如8:30的Shift In，当已经有一个8:00的Shift In行时。你想要删除冗余的行，只保留这些重复状态的第一行。
使用LAG来查看前一行的状态。
select id, date, time, status
from
(
select
t.*,
lag(status) over (partition by id order by date, time) as prev_status
from mytable t
)
where prev_status is null or prev_status <> status
order by id, date, time;
英文:
It seems you just want to list all Shift In and Shift Out rows, but there are some redundant rows, such as Shift In at 8:30 when there is already a row for Shift In at 8:00. You want to remove the redundant rows and only keep the first row of such recurring statuses.
Use LAG to see the status of the previous row.
select id, date, time, status
from
(
select
t.*,
lag(status) over (partition by id order by date, time) as prev_status
from mytable t
)
where prev_status is null or prev_status <> status
order by id, date, time;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论