SQL - Finding common rows based on a column (inter

问题:

Given the following table:

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

I need to select Apps which are common to given "n" machines. It's an intersect between "n" sets. However, I'm having a hard time coming up with a decent query.

I know that I can do something like this:

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

INTERSECT

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

That will give me

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

However, is there a way I can do it without having to know the number of machines upfront? Something like

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

回答1:

I think this should do it for the general case:

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

回答2:

You can use Row_Number function and Partition the Data with Machine Name

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

回答3:

You can parametrize the IN query

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 

Or you can even use a sub query to get all the values

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

回答4:

You can do this with a group by. Assuming there are no duplicate machines for a given app in the InstalledApps table:

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

If you have duplicate apps on the 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>

And, I'm thinking that if you don't want to count the number of machines, then you might need to use dynamic SQL or store the machine names in a temporary table.