SQL Server最大值检索

Question

我在database1中有以下3个表

create table database1..Table1 (id int ,datestamp datetime)
create table database1..Table2 (id int ,datestamp datetime)
create table database1..Table3(id int ,datestamp datetime)

这张表是database2格式的

create table database2..Table1 (id int ,datestamp datetime)

我想通过较大的日期戳值更新database2的Table1 .datestamp (在database1的3个表之间)

有没有比这个查询更好的方法(不使用case)：

update db2t1
set datestamp=
case    when db1t1.datestamp > db1t2.datestamp and db1t1.datestamp > db1t3.datestamp then db1t1.datestamp
        when db1t2.datestamp > db1t1.datestamp and db1t2.datestamp > db1t3.datestamp then db1t2.datestamp
        else db1t3.datestamp
    end
from database2..Table1 db2t1 inner join database1..Table1 db1t1 on db2t1.id=db1t1.id
inner join database1..Table2 db1t2 on db1t1.id=db1t2.id
inner join database1..Table3 db1t3 on db1t1.id=db1t2.id

这个查询将在一个巨大的表上执行

Answer 1

也许你可以这样做：

UPDATE db2t1
SET db2t1.datestamp = tbl2.MaxDate
from database2..Table1 db2t1
JOIN
(
    SELECT
        MAX(tbl.datestamp) AS MaxDate,
        tbl.id
    FROM
    (
        SELECT id,datestamp database1..Table1 UNION ALL
        SELECT id,datestamp database1..Table2 UNION ALL
        SELECT id,datestamp database1..Table3
    ) AS tbl
    GROUP BY
       tbl.id
) AS tbl2
ON tbl2.id=db2t1.id

Answer 2

在大桌子上做这件事的任何方法都是困难的。我将把它分成几批(基于ID的范围，假设它是聚集键)，并将查询更改为如下所示：

UPDATE T1
SET dateStamp = X.dateStamp
FROM database2..Table1 T1
JOIN (SELECT ID, MAX(dateStamp) dateStamp
    FROM (
            SELECT ID, dateStamp
            FROM table2 T2
            UNION ALL
            SELECT ID, dateStamp
            FROM table3 T3
         ) N
    GROUP BY ID) X ON X.ID = T1.ID
WHERE X.dateStamp > T1.dateStamp

为了减少T1被锁定的时间，您可以将派生表放入一个临时表中，然后根据结果更新T1。