时间戳

首页 > 解决方案 > 使用案例表达式从另一个表更新表

sql - 使用案例表达式从另一个表更新表

问题描述

当'statustype'=out时，我想从'dateattend'列中的表'dailydata'更新表'dailydatawh'中的列'statusout'

UPDATE DAILYDATAWH 
SET STATUSOUT=
(SELECT 
 (case when STATUSTYPE='OUT' then DATEATTEND end)as STATUSOUT
FROM DAILYDATA
GROUP By NIP,NAME,DEPARTMENT,DATEATTEND,STATUSTYPE)
WHERE STATUSOUT = NULL

表每日数据

表每日数据WH

--

在我尝试以下答案后

UPDATE DDW
SET    STATUSOUT = DD.DATEATTEND 
FROM   DAILYDATAWH DDW
       INNER JOIN DAILYDATA DD ON DDW.NIP = DD.NIP
                              AND DDW.NAME = DD.NAME
                              AND DDW.DEPARTMENT = DD.DEPARTMENT
WHERE  DD.STATUSTYPE = 'OUT'

此查询有效，但是当我在 DAILYDATA 表中输入新数据（NIP、NAME、DEPARTMENT、DATEATTEND、STATUSTYPE）时，“STATUSOUT”（DAILYDATAWH 表）中的新值仍然出现在前一个日期

表每日数据 表每日数据WH

这是查询的当前输出：

UPDATE DDW
SET    STATUSOUT = DD.DATEATTEND 
FROM   dailydatawh DDW
       INNER JOIN 
       (
           SELECT NIP, NAME, DEPARTMENT, DATEATTEND = MAX(DATEATTEND)
           FROM   dailydata 
           WHERE  STATUSTYPE = 'OUT'
           GROUP BY NIP, NAME, DEPARTMENT
       ) DD                    ON DDW.NIP = DD.NIP
                              AND DDW.NAME = DD.NAME
                              AND DDW.DEPARTMENT = DD.DEPARTMENT

电流输出

这是示例输入

示例输入表

这是示例输出

示例输出表

标签： sqlsql-serversql-updatecase

解决方案

您内部加入表格（这里我假设它基于列NIP，因为未提供实际信息）

UPDATE DDW
SET    STATUSOUT = DD.DATEATTEND 
FROM   dailydatawh DDW
       INNER JOIN dailydata DD ON DDW.NIP = DD.NIP
                              AND DDW.NAME = DD.NAME
                              AND DDW.DEPARTMENT = DD.DEPARTMENT
WHERE  DD.STATUSTYPE = 'OUT'

更新查询。 

UPDATE DDW
SET    STATUSOUT = DD.DATEATTEND 
FROM   dailydatawh DDW
       INNER JOIN 
       (
           SELECT NIP, NAME, DEPARTMENT, DATEATTEND = MAX(DATEATTEND)
           FROM   dailydata 
           WHERE  STATUSTYPE = 'OUT'
           GROUP BY NIP, NAME, DEPARTMENT
       ) DD                    ON DDW.NIP = DD.NIP
                              AND DDW.NAME = DD.NAME
                              AND DDW.DEPARTMENT = DD.DEPARTMENT
WHERE DDW.STATUSOUT IS NULL

更新的查询 #2。 

UPDATE DDW
SET    STATUSOUT = DD.DATEATTEND 
FROM   dailydatawh DDW
       CROSS APPLY
       (
           SELECT TOP 1 d.DATEATTEND
           FROM   dailydata d
           WHERE  d.STATUSTYPE = 'OUT'
           AND    d.NIP =  DDW.NIP
           AND    d.NAME = DDW.NAME
           AND    d.DEPARTMENT = DDW.DEPARTMENT
           ORDER BY d.ID DESC
       ) DD
WHERE  DDW.STATUSOUT IS NULL

这是将您的插入和更新组合到一个查询中的查询

; WITH CTE AS
(
    SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY NIP, NAME, DEPARTMENT 
                                          ORDER BY DATEATTEND)
    FROM   DAILYDATA
)
INSERT INTO DAILYDATAWH (NIP, NAME, DEPARTMENT, STATUSIN, STATUSOUT)
SELECT  NIP, NAME, DEPARTMENT, STATUSIN = MIN(DATEATTEND), STATUSOUT = MAX(DATEATTEND)
FROM    CTE
GROUP BY NIP, NAME, DEPARTMENT, (RN - 1) / 2

db<>小提琴

推荐阅读