大发龙虎首页    注册   登录
大发龙虎 = way to explore
大发龙虎 是一个大发龙虎关于 分享和探索的地方
现在注册
已注册用户请  登录
大发龙虎  ›  数据库

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

  •  
  •   Gatsbywl · 10 天前 · 1876 次点击

    Oracle

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

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

    uLtY8I.png

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

    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'
    大发龙虎关于   ·   FAQ   ·   API   ·   大发龙虎大发龙虎我 们 的愿景   ·   广告投放   ·   感谢   ·   实用小大发龙虎工具   ·   2665 人在线   最高记录 5043   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.3 · 19ms · UTC 12:42 · PVG 20:42 · LAX 05:42 · JFK 08:42
    ♥ Do have faith in what you're doing.