Question

SQLServer 语句怎么写

A表和B表的都有address、time、value三列，现在需要从A表和B表获取每个address的time值最大的value值然后将address、time、value 输出，请问这条SQL语句怎么写？

Answer 1

嵌套比较多，你慢慢看。
select D.address, D.time, D.value
from (select A.address, A.time, A.value
from A
union
select B.address, B.time, B.value
from B) D,
(select C.Address, max(C.time) as maxTime
from (select A.address, A.time, A.value
from A
union
select B.address, B.time, B.value
from B) C
group by C.address) E
where D.address = E.address
and D.time = E.maxTime

追问

佩服，能写出视图方式的吗？我是要把B、C、D三个表的内容分别于A表的相应字段进行上述操作

追答

CREATE OR REPLACE VIEW VW_MAX AS
select G.address, G.time, G.value
from (select A.address, A.time, A.value
from A
union
select B.address, B.time, B.value
from B
union
select C.address, C.time, C.value
from C
union
select D.address, D.time, D.value
from D) G,
(select E.Address, max(E.time) as maxTime
from (select A.address, A.time, A.value
from A
union
select B.address, B.time, B.value
from B
union
select C.address, C.time, C.value
from C
union
select D.address, D.time, D.value
from D) E
group by E.address) F
where G.address = F.address
and G.time = F.maxTime

思路一样的

追问

B\C\D三个表需要的字段不同 比如B表是B.address, B.time, B.value，C表的是address, time, otherValue（对应A表的otherValue）

追答

你可以把字段重命名成一样的

比如 C.otherValue as value

Answer 2

--2个表自己合并下就好
SELECT  *
FROM    (SELECT address,time,value,ROW_NUMBER () OVER (PARTITION BY address ORDER BY time DESC) AS N FROM 表1) AS A
WHERE   N=1;

追问

这个方法只能找到表A或者表B的数据吧，怎么样将两个表的最大值汇聚一个表格？
能写出视图方式的吗？我是要把B、C、D三个表的内容分别于A表的相应字段进行上述操作