SQL - 基于列查找共同行（交集）

Question

给定以下表格：

已安装应用程序

MachineName | AppName | Version
-------------------------------
machine01   | App01   | 1.1
machine01   | App02   | 1.0
machine01   | App03   | 1.5
machine02   | App04   | 2.0
machine02   | App02   | 1.0
machine02   | App01   | 1.1
machine03   | App06   | 7.9.9
machine03   | App01   | 1.1
machine03   | App07   | 11.5
machine03   | App02   | 1.0

我可以帮助您翻译以下涉及IT技术的内容，需要翻译的内容如下：

我需要选择在给定“n”台机器上常见的应用程序。这是“n”个集合之间的交集。然而，我很难想出一个合适的查询。

我知道我可以像这样做:

SELECT AppName, Version 
FROM InstalledApps 
WHERE MachineName = 'machine01'

INTERSECT

SELECT AppName, Version 
FROM InstalledApps 
WHERE MachineName = 'machine02' 

那将给我...

AppName | Version
------------------
App01   | 1.1
App02   | 1.0

然而，是否有一种方法可以在不事先知道机器数量的情况下完成此操作？类似于：

SELECT...... 
FROM InstalledApps 
WHERE MachineName IN ('machine01',...'machine99')

- Nasir

---

(MachineName, AppName, Version) 这个组合键是否允许出现重复的条目？ - Andriy M

Answer 1

我认为这对于一般情况应该足够了：

SELECT AppName, Version, COUNT(DISTINCT(MachineName)) AS MachineCount
FROM InstalledApps
GROUP BY
  AppName, Version
HAVING
  COUNT(DISTINCT(MachineName)) > 1

- bluevector

---

看起来你的 GROUP BY 子句有点问题（应该是“AppName，Version”而不是“MachineName”，但除此之外，这似乎是对问题最合理的回答了... - Tao

此外，为了满足提问者（暗示的？）只想针对特定机器名称执行此操作的要求，您可以添加一个WHERE子句。 - Tao

Answer 2

您可以使用 Row_Number 函数并按机器名称 Partition 数据。

Select AppName, [Version] From 
(
    Select Row_Number() Over(Partition By MachineName Order by AppName) RowId, * 
    From InstalledApps
)K
Where K.RowId = 2

- user1425057

Answer 3

你可以使用 group by 来实现这个功能。假设在 InstalledApps 表中，对于一个给定的应用程序没有重复的机器：

select apps
from InstalledApps
group by apps
having sum(case when machine in (<list of machines>) then 1 else 0 end) = <number of machines>

如果您的机器上有重复的应用程序：

select apps
from (select distinct apps, machine from InstalledApps) ia
group by apps
having sum(case when machine in (<list of machines>) then 1 else 0 end) = <number of machines>

我认为，如果您不想计算机器的数量，那么您可能需要使用动态SQL或将机器名称存储在临时表中。

- Gordon Linoff

Answer 4

您可以对IN查询进行参数化

Declare @value varchar(max)
Declare @sql varchar(max)
select @value = 'machine1,machine2,machine3'
SELECT @sql = 'SELECT * FROM InstalledApps  WHERE MachineName in (' + @value+ ')'

 exec sp_executeSQL @sql 

或者您甚至可以使用子查询来获取所有值

SELECT * FROM InstalledApps WHERE MachineName in (SELECT MachineName FROM InstalledApps    group by MachineName HAVING COUNT(DISTINCT(MachineName)) > 1)

- praveen

---

我认为你误解了问题。给定machine01和machine02，我需要获取安装在两台机器上的应用程序，排除仅存在于machine01或machine02上的任何内容。 - Nasir