sql-server - 检查匹配

Question

我有两张桌子@Cust 和@Bh。我需要通过“loaddate”字段从@Bh 表中获取最后一条记录，如果没有匹配项，则插入记录。

@Cust：

DECLARE @Cust TABLE(
    Custnumber INT,
    Flag NVARCHAR(10),
    data NVARCHAR(10),
    status NVARCHAR(10),
    loaddate DATETIME
)

INSERT @Cust (Custnumber
                ,Flag
                ,data
                ,status
                ,loaddate)
VALUES (123,N'Y',N'20170117',N'Test','2018-11-15 15:35:26.393')

@Bh：

DECLARE @Bh TABLE(
    Custnumber INT,
    Flag NVARCHAR(10),
    data NVARCHAR(10),
    status NVARCHAR(10),
    loaddate DATETIME
)

INSERT @Bh (Custnumber
                ,Flag
                ,data
                ,status
                ,loaddate)
VALUES (123,N'Y',N'20170117',N'','2018-11-09 15:35:26.393')
,(123,N'Y',N'20170117',N'Tests','2018-11-10 15:35:26.393')
,(123,N'Y',N'20170117',N'','2018-11-15 15:35:26.393')
,(123,N'Y',N'20170117',N'Test','2018-11-15 15:35:26.393')

结果：

INSERT INTO @Bh(Custnumber
                ,Flag
                ,data
                ,status
                ,loaddate) 
SELECT DISTINCT PC.Custnumber
                ,PC.Flag
                ,PC.data
                ,PC.status
                ,PC.loaddate
FROM @Bh  AS BH
    INNER JOIN @Cust AS PC ON PC.Custnumber = BH.Custnumber
                                        AND (ISNULL(PC.Flag, '''')  <> ISNULL(BH.Flag, '''')
                                        OR   ISNULL(PC.data, '''')  <> ISNULL(BH.data, '''')
                                        OR   ISNULL(PC.status, '''')  <> ISNULL(BH.status, '''')) 
WHERE BH.loaddate = (SELECT MAX(loaddate) FROM @Bh AS BH2 WHERE BH.[Custnumber] = BH2.[Custnumber])';

因为我没有完全正确的条件，所以插入记录，因为第 3 行与表 @Cust 中的条目不同，因此被添加到表 @Bh

这是我提出的一个解决方案，它适合我的情况，但也许有一些更简单的解决方案？

WHERE   NOT EXISTS(SELECT 1 FROM @Bh AS BH2 WHERE PC.Custnumber = BH2.Custnumber AND PC.Flag = bh2.Flag GROUP BY BH2.Custnumber HAVING CONVERT(varchar(10), max(bh2.[loaddate]), 101) = (SELECT CONVERT(varchar(10), max([loaddate]), 101) FROM @Bh AS BH3 WHERE BH3.Custnumber = BH2.Custnumber))
        OR NOT EXISTS(SELECT 1 FROM @Bh AS BH2 WHERE PC.Custnumber = BH2.Custnumber AND PC.data = bh2.data GROUP BY BH2.Custnumber HAVING CONVERT(varchar(10), max(bh2.[loaddate]), 101) = (SELECT CONVERT(varchar(10), max([loaddate]), 101) FROM @Bh AS BH3 WHERE BH3.Custnumber = BH2.Custnumber))
        OR NOT EXISTS(SELECT 1 FROM @Bh AS BH2 WHERE PC.Custnumber = BH2.Custnumber AND PC.status = bh2.status GROUP BY BH2.Custnumber HAVING CONVERT(varchar(10), max(bh2.[loaddate]), 101) = (SELECT CONVERT(varchar(10), max([loaddate]), 101) FROM @Bh AS BH3 WHERE BH3.Custnumber = BH2.Custnumber))

标签： sql-servertsql