时间戳

首页 > 解决方案 > 在条件下将值插入临时表并在插入时更改值（SQL Server）

sql-server - 在条件下将值插入临时表并在插入时更改值（SQL Server）

问题描述

我有两个临时表

＃铲

Equipment    Code     Start_Time
EX7016       1000     0
EX7016       4030     1200
EX7016       1000     1400

＃卡车

Equipment Shovel  Code    Start_Time
DT5188    EX7016  1002    0
DT5188    EX7016  1003    1000
DT5188    EX7016  1004    1500
DT5188    EX7016  1005    2000

我需要的逻辑是如果#shovel 表的代码> 2000，并且时间从卡车处于1003 代码时开始，然后将该时间作为1002 代码插入#truck 表中。一旦 Shovel 回到 1000 代码，将该时间插入为 1003 代码，如下所示：

Equipment  Shovel  Code    Start_Time
DT5188     Ex7016  1002    0
DT5188     Ex7016  1003    1000
DT5188     Ex7016  1002    1200
DT5188     Ex7016  1003    1400
DT5188     Ex7016  1004    1500
DT5188     Ex7016  1005    2000

标签： sql-serversql-server-2012

解决方案

我对此进行了快速尝试，这是一个可分多个步骤运行的解决方案。它可能不适合目的，但至少这是一个开始。

IF OBJECT_ID('tempdb..#shovel') IS NOT NULL
    DROP TABLE #shovel;
IF OBJECT_ID('tempdb..#truck') IS NOT NULL
    DROP TABLE #truck;
IF OBJECT_ID('tempdb..#matches1') IS NOT NULL
    DROP TABLE #matches1;
IF OBJECT_ID('tempdb..#matches2') IS NOT NULL
    DROP TABLE #matches2;

SELECT 'EX7016' AS Equipment, '1000' AS Code, '0' AS Start_Time
INTO #shovel
UNION ALL
SELECT 'EX7016' AS Equipment, '4030' AS Code, '1200' AS Start_Time
UNION ALL
SELECT 'EX7016' AS Equipment, '1000' AS Code, '1400' AS Start_Time

SELECT 'DT5188' AS Equipment, 'EX7016' AS Shovel, '1002' AS Code, '0' AS Start_Time
INTO #truck
UNION ALL
SELECT 'DT5188' AS Equipment, 'EX7016' AS Shovel, '1003' AS Code, '1000' AS Start_Time
UNION ALL
SELECT 'DT5188' AS Equipment, 'EX7016' AS Shovel, '1004' AS Code, '1500' AS Start_Time
UNION ALL
SELECT 'DT5188' AS Equipment, 'EX7016' AS Shovel, '1005' AS Code, '2000' AS Start_Time;

--Make a list of cases where the #shovel table has code > 2000, and the time starts when the truck is in a 1003 code
SELECT 
    s.Equipment, 
    s.Start_Time, 
    t.Shovel
INTO 
    #matches1
FROM 
    #shovel s 
    INNER JOIN #truck t ON t.Shovel = s.Equipment
WHERE 
    s.Code > '2000' 
    AND t.Code = '1003';

--Find cases where the shovel goes back to a 1000 code
SELECT
    m.Equipment,
    s.Start_Time,
    m.Shovel
INTO
    #matches2
FROM
    #matches1 m
    INNER JOIN #shovel s ON s.Equipment = m.Equipment AND s.Start_Time > m.Start_Time AND s.Code = '1000';

--Finally, insert matching rows back into the #truck table (do we only do this if there is a match in both tables?)
INSERT INTO #truck SELECT Equipment, Shovel, '1002' AS Code, Start_Time FROM #matches1;
INSERT INTO #truck SELECT Equipment, Shovel, '1003' AS Code, Start_Time FROM #matches2;

SELECT * FROM #truck ORDER BY Start_Time;

推荐阅读