sql - 如何优化查询？

我想计算上下接口的数量，并编写此代码，以从两个表Nodes and Interface中对它们进行计数。这段代码有效，但是我想知道其他任何方式或对该代码进行优化吗？计数它们的最佳方法是什么？

SELECT q1.NodeName, q1.Nup as up, q2.ndown as down FROM ( SELECT Nodes.NodeID AS NodeID, Interfaces.NodeID AS InterfaceID, Nodes.Caption AS NodeName, Interfaces.Status as Status, Count(Nodes.Caption) as Nup FROM Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID) WHERE ( ( (Interfaces.Status = '2')) ) GROUP BY Nodes.NodeID, Nodes.Caption, Interfaces.Status, Interfaces.NodeID ) AS q1 INNER JOIN (SELECTInterfaces.NodeID AS InterfaceID,Nodes.Caption AS NodeName,Interfaces.Status as Status,Count(Nodes.Caption) as ndownFROMNodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)WHERE( ( (Interfaces.Status = '1')))GROUP BY Nodes.NodeID, Nodes.Caption, Interfaces.Status, Interfaces.NodeID ) AS q2 ON (q1.NodeID = q2.InterfaceID)order by down Desc

最佳答案

您可以组合查询：

SELECT
    Nodes.Caption AS NodeName,
    Count(
      CASE WHEN Interfaces.Status = '2'
        THEN 1
        ELSE NULL
      END) as up,
    Count(
      CASE WHEN Interfaces.Status = '1'
        THEN 1
        ELSE NULL
      END) as down
FROM Nodes
  INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)
WHERE Interfaces.Status IN('1', '2')
GROUP BY  Nodes.NodeID, Nodes.Caption
order by 3 Desc

注意：在您的原始查询中，如果某个接口始终处于打开状态或始终处于关闭状态，则由于您使用的是INNER JOIN，因此该接口不会显示在结果集中。

关于sql - 如何优化查询？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/5198670/