V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
Gatsbywl
V2EX  ›  数据库

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

  •  
  •   Gatsbywl · 2019-10-12 09:11:32 +08:00 · 3059 次点击
    这是一个创建于 1629 天前的主题,其中的信息可能已经有所发展或是发生改变。

    Oracle

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

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

    uLtY8I.png

    21 条回复    2019-10-13 00:04:50 +08:00
    sadfQED2
        1
    sadfQED2  
       2019-10-12 09:14:15 +08:00
    U_ID 不是唯一的??那这咋匹配
    cwjokaka
        2
    cwjokaka  
       2019-10-12 09:17:59 +08:00
    JOIN 的结果用 DISTINCT 去重好像可以
    xuanbg
        3
    xuanbg  
       2019-10-12 09:21:52 +08:00
    这个数据 SQL 没办法做到一一配对。如果数据是有序的,你还能写代码用循环来处理。
    xuanbg
        4
    xuanbg  
       2019-10-12 09:22:58 +08:00
    非要 SQL 处理,只能存储过程用游标。这种方法严重不推荐!
    kiracyan
        5
    kiracyan  
       2019-10-12 09:23:52 +08:00
    如果 in out 按时间顺序匹配的话 你可以按 U_ID 分组 然后在对应 比如 U_ID=1 有 Gruop1 Group2, 这非唯一对应肯定要先处理原数据的
    ESeanZ
        6
    ESeanZ  
       2019-10-12 09:26:11 +08:00
    通过 min、max,根据 U_id 获取大当前 in 的最小 out 时间,
    xuanbg
        7
    xuanbg  
       2019-10-12 09:27:46 +08:00
    想了一下,还有一种间接的办法,就是给你的原始数据加一列 group_id,让每一对 IN_OUT 拥有相同且唯一的 id 就行了。然后你就能按 group_id 进行 group by 配对了。
    LeeSeoung
        8
    LeeSeoung  
       2019-10-12 09:33:01 +08:00
    这个还涉及到行转列的问题,就算 sql 写出来也是一坨,建议代码里逻辑处理
    tk2049jq
        9
    tk2049jq  
       2019-10-12 09:38:02 +08:00
    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
        10
    bluarry  
       2019-10-12 09:38:41 +08:00 via Android
    没用过 oracle,不知道可不可以用 group by 然后排个序
    oaix
        11
    oaix  
       2019-10-12 09:49:45 +08:00
    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
        12
    wwwwaaanng  
       2019-10-12 09:52:48 +08:00
    两条 sql union 一下?
    a87965028
        13
    a87965028  
       2019-10-12 09:53:08 +08:00   ❤️ 1
    ;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
        14
    anzu  
       2019-10-12 10:08:19 +08:00
    如果 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
        15
    a87965028  
       2019-10-12 10:13:21 +08:00
    @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
        16
    opengps  
       2019-10-12 10:17:54 +08:00
    不建议合并,看表结构很显然是物联网开关传感器的上报信息。实际上,这么处理会掩盖“漏点”问题。源头建议用程序接收时候处理成时间轴变化状态。也就是说保留原始数据,用程序直接读取源数据加工
    Gatsbywl
        17
    Gatsbywl  
    OP
       2019-10-12 11:50:51 +08:00
    @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
        18
    ESeanZ  
       2019-10-12 15:14:30 +08:00
    @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
        19
    wqzjk393  
       2019-10-12 15:43:55 +08:00
    case when 啊。。。
    jowenzzzzz
        20
    jowenzzzzz  
       2019-10-12 16:04:37 +08:00 via Android
    你是想原表数据转换到查询结果样式吧,不用 join.on 用分析函数应该可以解决
    reus
        21
    reus  
       2019-10-13 00:04:50 +08:00
    一群人讨论了半天都不知道有窗口函数?

    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'
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   3269 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 40ms · UTC 13:51 · PVG 21:51 · LAX 06:51 · JFK 09:51
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.