SQL Server : nested looping over two Selects

I have the following two queries that produce the results I need. Now the final output I truly need I would usually use python for after the results are returned, but unfortunately only SQL can be used.

我有以下两个查询产生我需要的结果。现在我真正需要的最终输出我通常会在返回结果后使用python,但不幸的是只能使用SQL。

Query A:

SELECT * 
FROM openquery(PROD, 'SELECT `status`, computer_name, device_type 
                      FROM assets 
                      WHERE (device_type="SERVER") 
                        AND (status="ACTIVE")')

Query B:

SELECT * 
FROM openquery(AppMap, 'SELECT `t1`.`uaid` AS `uaid`, `t3`.`computer_name`,
                        FROM ((`applications` `t1` 
                        JOIN `app_infrastructure` `t2` ON (((`t1`.`uaid` = `t2`.`uaid`)))) 
                        JOIN `infrastructure` `t3` ON ((`t2`.`infrastructure_id` = `t3`.`infrastructure_id`)));')

How I would want to process the results:

我想如何处理结果:

if a computer_name is in both A and B:
   final_row = ['computer_name', 1]
elseif a computer_name is in A but not B:
   final_row = ['computer_name', 0]
elseif a computer_name is in B but not A:
   final_row = ['computer_name', 2]

So my final query results need to look like those rows, does that make sense?

所以我的最终查询结果需要看起来像那些行,这有意义吗?

1 个解决方案

#1

In a stored procedure, use both queries to load table variables.

在存储过程中,使用两个查询来加载表变量。

Then do a FULL OUTER JOIN query, joining the two table variables on computer_name, and use a CASE expression to get your final_row value for each computer name.

然后执行FULL OUTER JOIN查询,连接computer_name上的两个表变量,并使用CASE表达式获取每个计算机名称的final_row值。

#1