sql - 如何优化我的查询？

Question

我想计算向下和向上的接口(interface)数量，并编写这段代码，从两个表节点和接口(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 (
SELECT
Interfaces.NodeID AS InterfaceID,
Nodes.Caption AS NodeName,
Interfaces.Status as Status,
Count(Nodes.Caption) as ndown
FROM
Nodes 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



Answer 1

您可以组合查询:

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

注意:在您的原始查询中，如果接口(interface)始终处于打开状态或始终处于关闭状态，则它不会显示在结果集中，因为您使用的是 INNER JOIN .