如何从两个表中获取最大值

Question

使用Sql Server

我想从两个表中获取最大值

表1

ID Total

101 100
102 600
.....

表2

ID Total

101 300
102 400
....

我希望能够根据ID从两个表中获取最大值。

期望输出：

ID Total

101 300 (max value in table2)
102 600 (max value in table1)
....
...

如何进行查询

需要查询帮助

- Gopal

Answer 1

SELECT
   ID, MAX(Total)
FROM
    (
    SELECT ID, Total FROM Table1
    UNION ALL
    SELECT ID, Total FROM Table2
    ) foo
GROUP BY
   ID

- gbn

Answer 2

; with
q_00 as (
select id, Total from Tbl_1
union all
select id, Total from Tbl_2
)
select id, max(Total)
from q_00
group by id
order by id ;

- Damir Sudarevic

Answer 3

另一个值得考虑的选项可能是

WITH T(ID, Total)
     AS (SELECT ID,
                MAX(Total)
         FROM   Table1
         GROUP  BY ID
         UNION ALL
         SELECT ID,
                MAX(Total)
         FROM   Table2
         GROUP  BY ID)
SELECT ID,
       MAX(Total) AS Total
FROM   T
GROUP  BY ID  

如果在这两个表中索引了 ID,Total，可能会得到更好的计划（未经测试）。

- Martin Smith

---

虽然现在已经测试过了，两者之间的差异非常微小，可以通过调整每个“ID”组中的“Total”行数来使其倾向于任一方。 - Martin Smith