SQL - 基于列找到共同的行（交叉）(SQL - Finding common rows bas

如下表：

InstalledApps

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

我需要选择Apps这些都是常见的给定的“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')

Answer 1:

我想这应该对于一般情况下做到这一点：

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

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

Answer 3:

您可以参数化的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)

Answer 4:

你可以用一组由做到这一点。 假设有在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或存储设备名称中的临时表。

文章来源: SQL - Finding common rows based on a column (intersect)