需要帮助优化update语句

Question

我有两张桌子：

create table CurrentDay (
ID int identity primary key,
ssn varchar(10), 
val money, 
CheckDate datetime, 
CurrentStatus tinyint)

create table PreviousDay (
ID int identity primary key,
ssn varchar(10), 
val money, 
CheckDate datetime, 
CurrentStatus tinyint)

我需要具有以下值的update字段CurrentDay.CurrentStatus：

CurrentDay;

• 112，中的
• 111，如果在PreviousDay.ssn中是与CurrentDay中相同的ssn，如果PreviousDay中的val与在PreviousDay.ssn中的相同，则PreviousDay.ssn中的val与CurrentDay中的相同，如果PreviousDay中的val比在CurrentDay中的要大，如果在PreviousDay.ssn中与CurrentDay中相同，如果PreviousDay的val小于CurrentDay中的大小；
• 114，如果没有与CurrentDay中相同的PreviousDay.ssn，即仅在CurrentDay中使用ssn (这是今天的信息)。

我编写了一些查询，但是我希望能够找到其他方法来完成这个任务，只使用PreviousDay和CurrentDay表之间的一个连接。显然，这不是一个非常幸运的kind...Here是我的变体：

Update CurrentDay 
Set CurrentStatus=case
when exists (select PreviousDay.ID from PreviousDay 
where PreviousDay.ssn=CurrentDay.ssn AND 
PreviousDay.val=CurrentDay.val  AND
PreviousDay.CheckDate=DATEADD(day,-1,CurrentDay.CheckDate))
then 111
when exists (select PreviousDay.ID from PreviousDay 
where PreviousDay.ssn=CurrentDay.ssn AND 
PreviousDay.val>CurrentDay.val  AND
PreviousDay.CheckDate=DATEADD(day,-1,CurrentDay.CheckDate))
then 112
when exists (select PreviousDay.ID from PreviousDay 
where PreviousDay.ssn=CurrentDay.ssn AND 
PreviousDay.val<CurrentDay.val  AND
PreviousDay.CheckDate=DATEADD(day,-1,CurrentDay.CheckDate))
then 113
when exists (select PreviousDay.ID from PreviousDay 
where PreviousDay.ssn!=CurrentDay.ssn AND 
PreviousDay.CheckDate=DATEADD(day,-1,CurrentDay.CheckDate))
then 114
end;

这里还有其他查询，但在这种情况下，我不能用值114更新CurrentDay.CurrentStatus‘fieid，因为这两个表中只有匹配的行：

Set Currentday.CurrentStatus=(select 111 where PreviousDay.val=CurrentDay.val union all select 112 where and PreviousDay.val>CurrentDay.val union all select 113 where and PreviousDay.val<CurrentDay.val /*union all select 114 */ ) from PreviousDay join CurrentDay on PreviousDay.ssn=CurrentDay.ssn and PreviousDay.CheckDate=DATEADD(day,-1,CurrentDay.CheckDate)

你还有别的主意吗？

Answer 1

类似于：

UPDATE
    C
Set
    CurrentStatus = CASE
            WHEN P.val = C.val THEN 111
            WHEN P.val > C.val THEN 112
            WHEN P.val < C.val THEN 113
            ELSE 114 ---this works because if P.VAL is null, that is no matching row
        END
FROM
    CurrentDay C
    LEFT JOIN
    PreviousDay P On C.ssn = P.ssn AND P.CheckDate = DATEADD(day, -1, C.CheckDate))

Answer 2

你可以试试这样的东西

UPDATE CurrentDay 
        SET CurrentStatus =
        CASE 
            WHEN cd.val = pd.val THEN 111
            WHEN cd.val < pd.val THEN 112
            WHEN cd.val > pd.val THEN 113
            WHEN pd.val IS NULL THEN 114
        END
FROM    CurrentDay cd LEFT JOIN
        PreviousDay pd ON   cd.ssn = pd.ssn
                        AND cd.CheckDate = DATEADD(d, 1, pd.CheckDate)

这是我的测试代码。使用val条目查看PreviousDay是否有效，或者完全删除PreviousDay条目以查看新条目选项。

DECLARE  @CurrentDay table (
ID int identity primary key,
ssn varchar(10), 
val money, 
CheckDate datetime, 
CurrentStatus tinyint)



DECLARE @PreviousDay table (
ID int identity primary key,
ssn varchar(10), 
val money, 
CheckDate datetime, 
CurrentStatus tinyint)

INSERT INTO @CurrentDay (ssn, val, CheckDate)  SELECT 1, 1, '02 Jan 2009'
INSERT INTO @PreviousDay (ssn, val, CheckDate)  SELECT 1, 0, '01 Jan 2009'

UPDATE @CurrentDay 
        SET CurrentStatus =
        CASE 
            WHEN cd.val = pd.val THEN 111
            WHEN cd.val < pd.val THEN 112
            WHEN cd.val > pd.val THEN 113
            WHEN pd.val IS NULL THEN 114
        END
FROM    @CurrentDay cd LEFT JOIN
        @PreviousDay pd ON  cd.ssn = pd.ssn
                        AND cd.CheckDate = DATEADD(d, 1, pd.CheckDate)

SELECT * FROM @CurrentDay