我正在尝试构建一个简单的查询,以了解工作人员何时进站和下班的时间。数据库记录人的姓名,如果他们的时钟进出和时间。它记录的比那个更多,但我不需要那些信息。
所以DB就是这样一个例子:
Id | User | In_out | Date_Time
----------------------------------
2 | Bob | In | 13/Oct/16 9:30:35AM
3 | Ken | In | 13/Oct/16 9:34:27AM
4 | Jon | In | 13/Oct/16 9:34:46AM
5 | Billy | In | 13/Oct/16 9:52:06AM
6 | Bob | Out | 13/Oct/16 4:30:05PM
7 | Jon | Out | 13/Oct/16 4:32:55PM我想在SQL中返回的结果是:
User | Time_In | Time_Out
-------------------------------
Bob | 9:30:35AM | 4:30:05PM
Jon | 9:34:46AM | 4:32:55PM我正在尝试的SQL是不正确的,我知道。但是我需要改变什么才能得到我想要的结果呢?
SELECT
Clock.Checkin_Checkout.User,
Clock.Checkin_Checkout.In_Out,
(SELECT TO_CHAR(Clock.Checkin_Checkout.DATETIME, 'hh:mi:ss AM')
FROM Clock.Checkin_Checkout
WHERE Clock.Checkin_Checkout.In_Out = 'In') AS "Time In",
To_Char(Clock.Checkin_Checkout.Create_Datetime, 'hh:mi:ss AM') AS "Time Out"
FROM
Clock.Checkin_Checkout
WHERE
Clock.Checkin_Checkout.In_Out = 'Out'
AND Clock.Checkin_Checkout.Datetime >= '13/Oct/2016'发布于 2016-10-16 21:55:59
我不认为你需要一个子查询。像下面这样的东西可能有效..。
select c1.User, c1.Date_Time as "Time_In", c2.Date_Time as "Time Out"
from Clock.Checkin_Checkout c1
inner join Clock.Checkin_Checkout c2 on c2.User = c1.User
where c1.In_Out = 'In'
and c2.In_Out = 'Out'请原谅可能的语法错误。我已经很久没有使用Oracle了,我也没有任何方法来测试它。
发布于 2016-10-16 22:09:24
我创建了一个示例这里。
在简单的假设中,对于单个名称,表中将有一个" In“,最多有一个"Out”,下面的查询应该可以工作:
Select c1.user,
to_char(MAX(CASE WHEN c1.In_Out='In' then c1.Date_Time end), 'hh:mi:ss AM') time_in,
to_char(MAX(CASE WHEN c1.In_Out='Out' then c1.Date_Time end), 'hh:mi:ss AM') time_out
From Clock.Checkin_Checkout as c1
group by c1.user
having MAX(CASE WHEN c1.In_Out='Out' then c1.Date_Time end) is not null;如果您也希望在没有“Out”的情况下,只需省略“拥有”子句即可。
编辑:更真实的场景
如果希望查询能够识别来自同一个人的多个移位,则可以使用以下查询:
select c1.user, c1.Date_Time as Time_In, c2.Date_Time as Time_Out
from Clock.Checkin_Checkout c1
join Clock.Checkin_Checkout c2
on c2.user= c1.user
where c1.In_Out= 'In'
and c2.In_Out= 'Out'
and c1.Date_Time <c2.Date_Time
and not exists (select 1 from Clock.Checkin_Checkout c3
where c1.user=c3.user
and c1.Date_Time <c3.Date_Time
and c3.Date_Time <c2.Date_Time )我还在雷克斯试验器上测试了这个解决方案。
免责声明:对于第二个场景,我从@user1751825查询开始,因为从他的查询(而不是从前一个查询)到这个解决方案很简单。
发布于 2016-10-16 22:47:54
还不清楚为什么您不想在输出中显示Ken和比利(显示他们已进入但从未超时),但如果这是您的要求,您可以这样做:
select user, min(date_time) as time_in, max(date_time) as time_out
from checkin_checkout
group by user
having count(*) = 2;这应该比任何基于连接的解决方案更有效。这就是查询所做的工作:首先,它按用户分组;然后保留有两个行的组(一个用于IN,另一个用于OUT);然后,它选择用户,时间越早(为该组从两个行中选出),并将其命名为time_in,并在以后的时间中调用它,并将其称为time_out。我觉得再简单不过了。
备注:
(1)这假定表已经按日期进行了“分组”(在您的表中,所有日期都是相同的)。否则,还需要按trunc(date_time)分组,并将trunc(date_time)包含在结果列中;除此之外,查询将以完全相同的方式工作。
(2)您有user列;我希望在您的实际数据中没有名称,而是一些对每个用户都是唯一的id或代码。"Bob“不是唯一的标识符。
(3)解决方案假设数据在插入基表时得到验证;因此,“用户”最多可以有两行,如果没有“行”,则不存在"Out“行,"Out”时间是"In“time之后的”Out“,等等。
https://stackoverflow.com/questions/40075820
复制相似问题