sql联接两个查询

ltqd579y 于 2021-08-09 发布在 Java

关注(0)|答案(1)|浏览(461)

我需要一些帮助来组合这两个查询，这样我就可以在一个视图中得到这个。
查询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信息和授权信息。如何连接这两个查询？我们可以使用common key nodes.nodeid加入，但不确定如何加入。任何帮助都将不胜感激。

sql Join sql-server

来源：https://stackoverflow.com/questions/62139699/sql-join-two-query

1条答案

按热度按时间

fykwrbwg1#

考虑使用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

赞(0）回复(0）举报 2021-08-09

我来回答

sql联接两个查询

1条答案

相关问题

热门标签

最新问答