oracle 使用外部变量的SQL连接

w9apscun 于 2023-08-04 发布在 Oracle

关注(0)|答案(2)|浏览(123)

我想将两个表连接在一起，一个表包含一个ID，以及输入项目时的TIME_STAMP。
| 时间输入| TIME_IN |
| --| ------------ |
| 2023年6月30日12：00：00| 30-JUN-2023 12:00:00 |
| 2023年6月29日08：00：00| 29-JUN-2023 08:00:00 |
| 2023年6月28日03：00：00| 28-JUN-2023 03:00:00 |
| 2023年6月27日19：00：00| 27-JUN-2023 19:00:00 |
| 2023年6月30日07：00：00| 30-JUN-2023 07:00:00 |
| 2023年6月29日12：00：00| 29-JUN-2023 12:00:00 |
第二个表包含ID的移动列表，按“命令”分类，1表示进入，2表示离开。
| 时间戳|指挥部| COMMAND |
| --|--| ------------ |
| 2023年6月30日12：10：00|二个| 2 |
| 2023年6月29日08：10：00|二个| 2 |
| 2023年6月28日03：10：00|二个| 2 |
| 2023年6月27日19：10：00|二个| 2 |
| 2023年6月26日08：10：00|二个| 2 |
我想连接这些表以创建一个如下表。
| 时间输入|暂停| TIME_OUT |
| --|--| ------------ |
| 2023年6月30日12：00：00| 2023年6月30日12：10：00| 30-JUN-2023 12:10:00 |
| 2023年6月29日08：00：00| 2023年6月29日08：10：00| 29-JUN-2023 08:10:00 |
| 2023年6月28日03：00：00| 2023年6月28日03：10：00| 28-JUN-2023 03:10:00 |
| 2023年6月27日19：00：00| 2023年6月27日19：10：00| 27-JUN-2023 19:10:00 |
我尝试了下面的，它返回了我所期望的-所有人的最新时间，如下所示。

SELECT 
A."TIME_IN",
MOVE.TIME_OUT,
FROM TIMESIN A
LEFT JOIN (
  SELECT
  ID,
  MAX(TIME_STAMP) AS "TIME_OUT"
  FROM MOVEMENTTAB M
  WHERE COMMAND = 2
  GROUP BY ID
) 
MOVE ON MOVE.ID = A.ID AND HIST.TIME_OUT > A.TIME_IN

字符串
| 时间输入|暂停| TIME_OUT |
| --|--| ------------ |
| 2023年6月30日12：00：00| 2023年6月30日12：10：00| 30-JUN-2023 12:10:00 |
| 2023年6月29日08：00：00| 2023年6月30日12：10：00| 30-JUN-2023 12:10:00 |
| 2023年6月28日03：00：00| 2023年6月30日12：10：00| 30-JUN-2023 12:10:00 |
| 2023年6月27日19：00：00| 2023年6月30日12：10：00| 30-JUN-2023 12:10:00 |
所以我想如果我把MAX换成MIN，它会返回每个示例的最低时间，但我想我得到了下面的结果，尽管TIME_OUT列显示为NULL，因为26 th Jun从'TIMESIN'表中省略了。
| 时间输入|暂停| TIME_OUT |
| --|--| ------------ |
| 2023年6月30日12：00：00| 2023年6月26日08：10：00| 26-JUN-2023 08:10:00 |
| 2023年6月29日08：00：00| 2023年6月26日08：10：00| 26-JUN-2023 08:10:00 |
| 2023年6月28日03：00：00| 2023年6月26日08：10：00| 26-JUN-2023 08:10:00 |
| 2023年6月27日19：00：00| 2023年6月26日08：10：00| 26-JUN-2023 08:10:00 |
有没有办法从外部使用JOIN中的变量，比如WHERE TIME_OUT > A.TIME_IN，或者有没有我想不出的更好的解决方案？
我曾尝试使用SELECT语句添加一列，但这会减慢进程的加载速度。

oracle

来源：https://stackoverflow.com/questions/76592151/sql-join-using-outside-variable

2条答案

按热度按时间

avkwfej41#

在Oracle 12中，您可以使用LATERAL join和FETCH FIRST ROWS ONLY来获取最新的time-in：

SELECT m.id,
       t.time_in,
       m.time_stamp AS time_out
FROM   movementtab m
       CROSS JOIN LATERAL (
         SELECT time_in
         FROM   timesin t
         WHERE  t.time_in <= m.time_stamp
         AND    t.id = m.id
         ORDER BY t.time_in DESC
         FETCH FIRST ROW ONLY
       ) t

字符串
其中，对于样本数据：

CREATE TABLE timesin (ID, TIME_IN) AS
SELECT 1, DATE '2023-06-30' + INTERVAL '12:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 1, DATE '2023-06-29' + INTERVAL '08:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 1, DATE '2023-06-28' + INTERVAL '03:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 1, DATE '2023-06-27' + INTERVAL '19:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 2, DATE '2023-06-30' + INTERVAL '07:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 2, DATE '2023-06-29' + INTERVAL '12:00:00' HOUR TO SECOND FROM DUAL;

CREATE TABLE movementtab (ID, TIME_STAMP, COMMAND) AS
SELECT 1, DATE '2023-06-30' + INTERVAL '12:10:00' HOUR TO SECOND, 2 FROM DUAL UNION ALL
SELECT 1, DATE '2023-06-29' + INTERVAL '08:10:00' HOUR TO SECOND, 2 FROM DUAL UNION ALL
SELECT 1, DATE '2023-06-28' + INTERVAL '03:10:00' HOUR TO SECOND, 2 FROM DUAL UNION ALL
SELECT 1, DATE '2023-06-27' + INTERVAL '19:10:00' HOUR TO SECOND, 2 FROM DUAL UNION ALL
SELECT 1, DATE '2023-06-26' + INTERVAL '08:10:00' HOUR TO SECOND, 2 FROM DUAL;

型
输出：
| 时间输入|暂停| TIME_OUT |
| --|--| ------------ |
| 2023-06-30 12：00：00| 2023-06-30 12：10：00| 2023-06-30 12:10:00 |
| 2023-06-29 08：00：00| 2023-06-29 08：10：00| 2023-06-29 08:10:00 |
| 2023-06-28 03：00：00| 2023-06-28 03：10：00| 2023-06-28 03:10:00 |
| 2023-06-27 19：00：00| 2023-06-27 19：10：00| 2023-06-27 19:10:00 |
fiddle

赞(0）回复(0）举报 2023-08-04

3b6akqbq2#

您可以使用UNION ALL连接两个表，然后使用LAST_VALUE分析函数查找每个超时的最近前一个超时：

SELECT id,
       time_in,
       ts AS time_out
FROM   (
  SELECT id,
         ts,
         command,
         LAST_VALUE(CASE command WHEN 1 THEN ts END) IGNORE NULLS
           OVER (PARTITION BY id ORDER BY ts) AS time_in
  FROM   (
    SELECT id, time_in AS ts, 1 AS command
    FROM   timesin
  UNION ALL
    SELECT id, time_stamp, command
    FROM   movementtab
    WHERE  command = 2
  )
)
WHERE  command = 2;

字符串
其中，对于样本数据：

CREATE TABLE timesin (ID, TIME_IN) AS
SELECT 1, DATE '2023-06-30' + INTERVAL '12:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 1, DATE '2023-06-29' + INTERVAL '08:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 1, DATE '2023-06-28' + INTERVAL '03:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 1, DATE '2023-06-27' + INTERVAL '19:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 2, DATE '2023-06-30' + INTERVAL '07:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 2, DATE '2023-06-29' + INTERVAL '12:00:00' HOUR TO SECOND FROM DUAL;

CREATE TABLE movementtab (ID, TIME_STAMP, COMMAND) AS
SELECT 1, DATE '2023-06-30' + INTERVAL '12:10:00' HOUR TO SECOND, 2 FROM DUAL UNION ALL
SELECT 1, DATE '2023-06-29' + INTERVAL '08:10:00' HOUR TO SECOND, 2 FROM DUAL UNION ALL
SELECT 1, DATE '2023-06-28' + INTERVAL '03:10:00' HOUR TO SECOND, 2 FROM DUAL UNION ALL
SELECT 1, DATE '2023-06-27' + INTERVAL '19:10:00' HOUR TO SECOND, 2 FROM DUAL UNION ALL
SELECT 1, DATE '2023-06-26' + INTERVAL '08:10:00' HOUR TO SECOND, 2 FROM DUAL;

型
输出：
| 时间输入|暂停| TIME_OUT |
| --|--| ------------ |
| * 空 *| 2023-06-26 08：10：00| 2023-06-26 08:10:00 |
| 2023-06-27 19：00：00| 2023-06-27 19：10：00| 2023-06-27 19:10:00 |
| 2023-06-28 03：00：00| 2023-06-28 03：10：00| 2023-06-28 03:10:00 |
| 2023-06-29 08：00：00| 2023-06-29 08：10：00| 2023-06-29 08:10:00 |
| 2023-06-30 12：00：00| 2023-06-30 12：10：00| 2023-06-30 12:10:00 |
fiddle

赞(0）回复(0）举报 2023-08-04

我来回答

oracle 使用外部变量的SQL连接

2条答案

相关问题

热门标签

最新问答