时间戳

首页 > 解决方案 > Sql 大于平均 2 个表

sql - Sql 大于平均 2 个表

问题描述

我很难找到解决方案。

这就是问题：给出具有最高平均干预单位（eenheden intin interventie）的操作系统的名称。平均值是干预单元总数除以具有该操作系统的服务器总数。

这是数据库的样子：

在此处输入图像描述

这是我想出的，我不知道这是否可行

SELECT 
    os 
FROM 
    servers s 
LEFT OUTER JOIN 
    (SELECT 
         serial, 
         COUNT(serial) AS aantalinterventies 
     FROM   
         interventies) AS interventies USING (serial) 
GROUP BY        
    os 
HAVING
    aantalinterventies/Count(s.serial) >= ALL (SELECT aantalinterventies2 / COUNT(se.serial) 
                                               FROM servers s 
                                               LEFT OUTER JOIN 
                                                   (SELECT 
                                                        serial, 
                                                        COUNT(serial) AS aantalinterventies2
                                                    FROM interventies) AS interventies USING (serial)) AS interventiesrest

这是我找到的答案：感谢 simonare！

selet os from (select os, sum(eenheden) / count( distinct serial) avg_unit
  from server s
  inner join intervantie i on i.FK = s.PK
  group by os
  order by avg_unit desc
) as T
where avg_unit = (select max(avg_unit) from (select os, sum(eenheden) / count( distinct serial) avg_unit
  from server s
  inner join intervantie i on i.FK = s.PK
  group by os
  order by avg_unit desc
) as T)

标签： sql

解决方案

这是通用 SQL，如果您标记您的 RDBMS 系统，您可以获得更具体的答案

with T as 
(
  select os, avg(eenheden) avg_unit
  from server s
  inner join intervantie i on i.FK = s.PK
  group by os
  order by avg_unit desc
)
selet os from T 
where avg_unit = (select max(avg_unit) from T)

with T as 
(
  select os, sum(eenheden) / count( distinct serial) avg_unit
  from server s
  inner join intervantie i on i.FK = s.PK
  group by os
  order by avg_unit desc
)
selet os from T 
where avg_unit = (select max(avg_unit) from T)

推荐阅读