SQL 查询问题，请教一下大家。

[Gatsbywl]

Oracle

• 左边是原表数据，时间是一个字段，现在我做报表需要的数据格式如右边所示。
• 我用了 JOIN...ON..，但是存在一个一对多的问题，一个 IN 匹配多个 OUT，所以无果。

---

所以特来此请教各位大佬们有没有好的方法，怎么实现？ SQL or PL/SQL ？ 不胜感激！

[sadfQED2]

U_ID 不是唯一的？？那这咋匹配

[cwjokaka]

JOIN 的结果用 DISTINCT 去重好像可以

[xuanbg]

这个数据 SQL 没办法做到一一配对。如果数据是有序的，你还能写代码用循环来处理。

[xuanbg]

非要 SQL 处理，只能存储过程用游标。这种方法严重不推荐！

[kiracyan]

如果 in out 按时间顺序匹配的话 你可以按 U_ID 分组 然后在对应 比如 U_ID=1 有 Gruop1 Group2, 这非唯一对应肯定要先处理原数据的

[ESeanZ]

通过 min、max，根据 U_id 获取大当前 in 的最小 out 时间,

[xuanbg]

想了一下，还有一种间接的办法，就是给你的原始数据加一列 group_id，让每一对 IN_OUT 拥有相同且唯一的 id 就行了。然后你就能按 group_id 进行 group by 配对了。

[LeeSeoung]

这个还涉及到行转列的问题，就算 sql 写出来也是一坨，建议代码里逻辑处理

[tk2049jq]

select
a.U_ID,
a.DATE_TIME as IN_TIME,
b.DATE_TIME as OUT_TIME
from (select * from tb_1 where IN_OUT = 'IN') a
join (select * from tb_1 where IN_OUT = 'OUT') b
on a.U_ID = b.U_ID

[bluarry]

没用过 oracle，不知道可不可以用 group by 然后排个序

[oaix]

JOIN 之后再对 U_ID，IN_TIME 做个分组，取最小的 OUT_TIME
select U_ID, IN_TIME, min(OUT_TIMES) OUT_TIME
from (select U_ID, a.DATE_TIME IN_TIME, b.DATE_TIME OUT_TIMES
from TT a
join TT b on a.U_ID = b.U_ID and a.DATE_TIME < b.DATE_TIME
where a.IN_OUT = 'IN'
and b.IN_OUT = 'OUT') t
group by U_ID, IN_TIME

[wwwwaaanng]

两条 sql union 一下？

[a87965028]

;with TT_IN as (
select *, ROW_NUMBER() over(partition by U_ID order by DATE_TIME) as rn
from TT where IN_OUT = 'IN'
),
TT_OUT as (
select *, ROW_NUMBER() over(partition by U_ID order by DATE_TIME) as rn
from TT where IN_OUT = 'OUT'
)
select TT_IN.U_ID, TT_IN.DATE_TIME as IN_TIME, TT_OUT.DATE_TIME as OUT_TIME
from TT_IN
left join TT_OUT on TT_IN.rn = TT_OUT.rn

如果用 sql server 的话，应该就是这么写。可以参考一下

[anzu]

如果 in out 是严格匹配，有 in 必有 out 的情况下，可以利用行号进行匹配。
这是 mysql 的，假设表名是 inout

SELECT t_in.U_ID, t_in.DATE_TIME AS in_time, t_out.DATE_TIME AS out_time
FROM
(SELECT
@rowNum1:=@rowNum1 + 1 AS n, i.*
FROM
`inout` i
, (SELECT @rowNum1:=0) tn
WHERE in_out = 'in'
ORDER BY DATE_TIME
) t_in
LEFT JOIN
(SELECT
@rowNum2:=@rowNum2 + 1 AS n, i.*
FROM
`inout` i
, (SELECT @rowNum2:=0) tn
WHERE in_out = 'out'
ORDER BY DATE_TIME
) t_out ON t_in.n=t_out.n

[a87965028]

@a87965028 #13 最后一行写少了
select TT_IN.U_ID, TT_IN.DATE_TIME as IN_TIME, TT_OUT.DATE_TIME as OUT_TIME
from TT_IN
left join TT_OUT on TT_IN.rn = TT_OUT.rn and TT_IN.U_ID = TT_OUT.U_ID

[opengps]

不建议合并，看表结构很显然是物联网开关传感器的上报信息。实际上，这么处理会掩盖“漏点”问题。源头建议用程序接收时候处理成时间轴变化状态。也就是说保留原始数据，用程序直接读取源数据加工

[Gatsbywl]

@ESeanZ @a87965028
谢谢大家！我写完了。
思路是
1. 先分别选出 IN 和 OUT 的数据
2. 再 LEFT JOIN ON （出的时间晚于进的时间）
3. 最后根据人员和进入时间分组，出的时间排序，每一个进入时间选择最早出的时间（ RN = 1 ）

SELECT T3.F_ID
, T3.F_NAME
, T3.IN_T
, T3.OUT_T
, T3.RN
FROM (
SELECT T1.F_ID
, T1.F_NAME
, T1.DATE_TIME IN_T
, T2.DATE_TIME OUT_T
, ROW_NUMBER() OVER(PARTITION BY T1.F_ID, T1.DATE_TIME
ORDER BY T2.DATE_TIME) RN
FROM
(SELECT F1.DATE_TIME
, F1.F_ID
, F1.F_NAME
, F1.F_DEPART
, F1.IN_OUT
FROM ADMIN.FAB_TIME F1
WHERE F1.IN_OUT = '001-正常进入开门'
AND F1.DATE_TIME BETWEEN
TO_DATE(20191001000000, 'YYYY-MM-DD HH24:MI:SS')
AND TO_DATE(20191002000000, 'YYYY-MM-DD HH24:MI:SS')
) T1
LEFT JOIN
(SELECT F1.DATE_TIME
, F1.F_ID
, F1.F_NAME
, F1.F_DEPART
, F1.IN_OUT
FROM ADMIN.FAB_TIME F1
WHERE F1.IN_OUT = '002-正常外出开门'
AND F1.DATE_TIME BETWEEN
TO_DATE(20191001000000, 'YYYY-MM-DD HH24:MI:SS')
AND TO_DATE(20191002000000, 'YYYY-MM-DD HH24:MI:SS')
) T2
ON T1.F_ID = T2.F_ID
AND T1.DATE_TIME <= T2.DATE_TIME
ORDER BY T1.F_ID,T1.DATE_TIME
) T3
WHERE T3.RN = 1
;

[ESeanZ]

@Gatsbywl 老哥你这代码量有点多啊
粗略写了一段 应该没啥毛病（环境 Mysql，某些地方应该不一样）
SELECT InTable.u_id,InTable.date_titme AS In_Time, (SELECT MIN(date_titme)
FROM demo_1 AS OutTable WHERE OutTable.date_titme>InTable.date_titme AND OutTable.In_Out="Out") AS Out_time
FROM demo_1 AS InTable WHERE InTable.In_Out="In"

[wqzjk393]

case when 啊。。。

[jowenzzzzz]

你是想原表数据转换到查询结果样式吧，不用 join.on 用分析函数应该可以解决

[reus]

一群人讨论了半天都不知道有窗口函数？

select
date_time as in_time,
lead(date_time, 1) over (partition by u_id order by u_id asc, date_time asc) as out_time
from t
where in_out = 'in'