sql - SQL Join 两个查询

Question

我需要一些帮助来组合这两个查询，以便我可以在一个视图中得到它。

查询 1

select t.*, n.caption, n.description
from (
    select NodeID, 
              count(distinct cpuindex) as number_of_cpu, 
                case 
                           When count(distinct cpuindex) < 8 THEN 1
                           Else count(distinct cpuindex)/8 
                           End AS number_of_cores
    from CPUMultiLoad_Detail (nolock) where nodeid in (select nodeid from nodesdata)
       group by NodeID
   ) as t
inner join NodesData as n (nolock) on n.nodeid = t.nodeid
where n.description NOT Like '%Windows%'
order by n.description

查询 2

SELECT D.Environment, B.Name, C.Caption, A.ComponentStatisticData, A.ErrorMessage
FROM [APM_CurrentStatistics] A, APM_Application B, NodesData C
join NodesCustomProperties D on D.NodeID= C.NodeID
WHERE 
A.ApplicationID=B.ID AND
A.NodeID=C.NodeID AND 
B.Name IN ('Oracle Database Licensing') 

我想加入第一个查询和第二个查询，所以我在同一个表中有 CPU 信息和许可信息。如何加入这两个查询？我们可以使用公共密钥 Nodes.NodeID 来加入，但不确定如何加入。任何帮助将不胜感激。

标签： sqlsql-serverjoin

Answer 1

考虑使用 CTE将包含不同NodeID的第一个查询的内部聚合子查询连接到第二个查询。此外，使用显式JOIN（SQL 中的当前标准）并注意不良习惯：使用表别名，如 (a, b, c)并使用更多信息的表别名。

WITH agg AS 
   (
    select NodeID, 
           count(distinct cpuindex) as number_of_cpu, 
           case 
               when count(distinct cpuindex) < 8 THEN 1
               else count(distinct cpuindex) / 8 
           end AS number_of_cores
    from CPUMultiLoad_Detail 
    where nodeid in (select nodeid from nodesdata)
    group by NodeID
   )

SELECT cp.Environment, app.Name, n.Caption,
       cs.ComponentStatisticData, cs.ErrorMessage,
       agg.NodeID, agg.number_of_cpu, agg.number_of_cores, n.description
FROM APM_CurrentStatistics cs 
INNER JOIN APM_Application app 
  ON cs.ApplicationID = app.ID
  AND app.Name IN ('Oracle Database Licensing') 
INNER JOIN NodesData n
  ON cs.NodeID = n.NodeID
  AND n.description NOT LIKE '%Windows%'
INNER JOIN NodesCustomProperties cp 
  ON cp.NodeID = n.NodeID
INNER JOIN agg
  ON cs.NodeID = agg.NodeID
ORDER BY n.description