Sql 我们可以使用连接改进包含多个子查询的查询吗

我不确定这是否真的是faster，但它是一种稍微更面向集合的方法，使用PIVOT并连接回细节:https://dbfiddle.uk/Ebw30T90

select *
from
(
    SELECT Latest.hostname, sd.category, sd.active, latest.metric, v.value
    FROM (
        SELECT hostname,metric, MAX(lastupdated) lastupdated
        FROM server_stat
        GROUP BY hostname,metric
    ) as Latest
    INNER JOIN server_stat v ON Latest.hostname = v.hostname AND Latest.metric = v.metric AND Latest.lastupdated = v.lastupdated
    INNER JOIN server_details sd on Latest.hostname = sd.hostname
) x
pivot
(
  max(value)
  for metric in([cpu],[ram],[disk])
) pvt

这些数据的问题在于，聚合值(In this case 101)不是我们希望看到的值，而是我们想要的MAX(lastupdated)行中的value列，这使得使用PIVOT变得更加困难.

关键是先准备好要加到PIVOT的那一套，用GROUP BY代替TOP 1/LIMIT 1，然后再加回server_stat表得到value

SELECT Latest.hostname, sd.category, sd.active, latest.metric, v.value
FROM (
    SELECT hostname,metric, MAX(lastupdated) lastupdated
    FROM server_stat
    GROUP BY hostname,metric
) as Latest
INNER JOIN server_stat v ON Latest.hostname = v.hostname AND Latest.metric = v.metric AND Latest.lastupdated = v.lastupdated
INNER JOIN server_details sd on Latest.hostname = sd.hostname

一百零二

Update: Thanks to Joel Coehoorn!个

这是一件美好的事情；)我们可以将这种方法与this solution by Joel Coehoorn相结合，以消除自行联接以访问相关数据的GROUP BY.我真的不能 comments window queries和group by的性能，但用这种方式管理的代码要少得多.

select *
from
(
    -- Thanks Joel Coehoorn!
    SELECT distinct sd.hostname, sd.category, sd.active, metric
       , first_value(value) over (partition by sd.hostname, ss.metric 
                              order by lastupdated desc) value
    FROM Server_Details sd
    INNER JOIN Server_Stat ss on ss.hostname = sd.hostname
) x
pivot
(
  max(value)
  for metric in([cpu],[ram],[disk])
) pvt

小提琴更新:(查看最后一个查询)https://dbfiddle.uk/7D9lGpZI