如何使用子查询进行计算

Question

我无法计算出

(比率=(与协议的连接数)/(连接总数))

select 
     traffic.protocol, 
     ratio
from 
     webserver 
     inner join traffic on webserver.ip = traffic.webserver_ip
where 
     ratio = (select 
                   traffic.*,
                   count(traffic.sourceip) 
              from 
                   webserver 
                   inner join traffic on webserver.ip = traffic.webserver_ip 
              group by 
                   traffic.protocol) /
             (select 
                   count(webserver.ip) 
              from 
                   webserver 
                   inner join traffic on webserver.ip = traffic.webserver_ip) 
              group by 
                   traffic.protocol);

Answer 1

试试这个查询，看看它是否有效。

SELECT traffic.protocol, SoIP/WsIP AS "Ratio"
FROM traffic INNER JOIN
(SELECT traffic.*,COUNT(traffic.sourceip) AS "SoIP" FROM traffic GROUP BY traffic.protocol) tA
ON traffic.webserver_ip=tA.webserver_ip INNER JOIN
(SELECT ip,COUNT(webserver.ip) AS "WsIP" FROM webserver GROUP BY ip) tB 
ON traffic.webserver_ip=tB.ip;

如果您有错误和/或其他想要添加的条件，请提供一个示例数据供我们使用。