Início / dba / Perguntas / 207488
Accepted
Martin Surasky
Asked: 2018-05-23 06:00:30 +0800 CST

Pergunta de consulta SQL (itens que não possuem algum valor em uma coluna)

  • 772

Aqui está um enigma que preciso resolver com algum T-SQL Magic, tentei e falhei.

É uma dessas coisas que, antes de pensar na solução real, acho que posso resolver em 3 minutos com um pouco de T-SQL, mas quando a "borracha encontra a estrada" não consigo imaginar os operadores certos para alcançar essa lógica.

Gostaria de saber se alguém na comunidade do Stack Exchange pode encontrar uma solução elegante para este. Acho que com uma combinação de 3 ou 4 consultas usando tabelas temporárias ou cursores eu poderia encontrar a solução, mas não seria baseada em SET ...

Eu tenho uma tabela que se parece com isso (mais ou menos...)

CREATE TABLE Computers (
   ID               int IDENTITY(1,1),
   ComputerName     varchar(255),
   OS               varchar(255),
   AppName          varchar(255),
   AppVersion       varchar(10)
)

Portanto, cada linha tem um nome de computador, um valor de sistema operacional (que pode ser Windows 7 OU Windows 10) e, em seguida, uma descrição de um aplicativo (por exemplo, AppName = "Notepad ++", AppVersion = "7.5.6")

ComputerName   | OS           | AppName   | AppVersion
Computer 1     | Windows 7    | App 1     | 1.0
Computer 1     | Windows 7    | App 2     | 1.0
Computer 1     | Windows 7    | App 3     | 1.0
Computer 2     | Windows 10   | App 4     | 1.0
Computer 2     | Windows 10   | App 5     | 1.0
Computer 3     | Windows 10   | App 4     | 1.0
Computer 4     | Windows 7    | App 4     | 1.0
Computer 4     | Windows 7    | App 5     | 1.0
Computer 5     | Windows 7    | App 1     | 1.0
Computer 5     | Windows 7    | App 4     | 1.0
Computer 5     | Windows 7    | App 5     | 1.0

A forma como funciona é assim: se um computador tiver 10 aplicativos instalados, você terá 10 linhas nesta tabela (uma para cada aplicativo). O nome do computador e o sistema operacional serão repetidos (por favor, não inicie uma discussão sobre a 2ª forma normal... esta é estritamente uma pergunta sobre uma consulta para resolver um problema).

Então, o problema que preciso resolver é: preciso encontrar uma lista de computadores ONDE o sistema operacional é o Windows 7 (isso é EAAAASY), mas ...

Esses computadores só devem aparecer na minha lista se TODOS os aplicativos relatados nesta tabela para esse PC forem relatados como sendo usados ​​em outros computadores com Windows 10.

Se um computador com Windows 7 tiver aplicativos que não estão listados em um computador com Windows 10, eles não devem aparecer nesta lista. Todo e qualquer aplicativo para computador deve estar em conformidade com esta regra para que o computador se qualifique na minha lista.

A ideia é saber que os computadores que estou recebendo no meu SELECT têm todos os aplicativos que eu sei que funcionam para o Windows 10, então eles devem ser mais seguros para migrar.

Amostra:

Para quem está tentando isso em casa, aqui está um CREATE TABLE e alguns INSERTs, você tem a ideia certa de como são meus dados

-- Create the table
 CREATE TABLE Computers (
    ComputerName    varchar(255),
    OS              varchar(255),
    AppName         varchar(255),
    AppVersion      varchar(10)
 )

 -- Insert some values...
 INSERT INTO Computers
 VALUES 
 ('Computer 1', 'Windows 7', 'App 1', '1.0'),
 ('Computer 1', 'Windows 7', 'App 2', '1.0'),
 ('Computer 1', 'Windows 7', 'App 3', '1.0'),
 ('Computer 2', 'Windows 10', 'App 4', '1.0'),
 ('Computer 2', 'Windows 10', 'App 5', '1.0'),
 ('Computer 3', 'Windows 10', 'App 4', '1.0'),
 ('Computer 4', 'Windows 7', 'App 4', '1.0'),
 ('Computer 4', 'Windows 7', 'App 5', '1.0'),
 ('Computer 5', 'Windows 7', 'App 1', '1.0'),
 ('Computer 5', 'Windows 7', 'App 4', '1.0'),
 ('Computer 5', 'Windows 7', 'App 5', '1.0')

  • Com este exemplo, o único computador que se qualifica para minha consulta seria o Computador 4, pois é o único computador com Windows 7 em que TODOS os aplicativos associados a ele (Aplicativos 4 e 5) também existem em pelo menos uma linha para um PC com Windows 10.

  • O computador 5 não se qualifica porque um dos aplicativos associados a ele ("Aplicativo 1") só existe neste conjunto em outro computador Windows 7 (Computador 1), portanto não posso dizer que este aplicativo já está funcionando em um PC com Windows 10 .

  • Os computadores 2 e 3 são Windows 10. Então, isso deve ser suficiente para descartá-los....

  • O computador 1 também NÃO deve se qualificar, todos os seus aplicativos não estão sendo executados em PCs com Windows 10.

Espero que faça sentido...

select t-sql

2 respostas

  1. Best Answer

    Acredito que a consulta a seguir fornecerá o nome correto do computador

     select ComputerName from dbo.computers
 where os= 'windows 7'
 except
 select ComputerName from dbo.computers
 where os= 'windows 7'
 and appname not in (select appname from dbo.computers where os='windows 10')

    O resultado é:

    insira a descrição da imagem aqui

    • 2

  2. Outra abordagem 

    DECLARE @t TABLE (ComputerName varchar(255), OS varchar(255), AppName varchar(255), AppVersion varchar(10))

 INSERT INTO @t VALUES 
 ('Computer 1', 'Windows 7',  'App 1', '1.0'),
 ('Computer 1', 'Windows 7',  'App 2', '1.0'),
 ('Computer 1', 'Windows 7',  'App 3', '1.0'),
 ('Computer 2', 'Windows 10', 'App 4', '1.0'),
 ('Computer 2', 'Windows 10', 'App 5', '1.0'),
 ('Computer 3', 'Windows 10', 'App 4', '1.0'),
 ('Computer 4', 'Windows 7',  'App 4', '1.0'),
 ('Computer 4', 'Windows 7',  'App 5', '1.0'),
 ('Computer 5', 'Windows 7',  'App 1', '1.0'),
 ('Computer 5', 'Windows 7',  'App 4', '1.0'),
 ('Computer 5', 'Windows 7',  'App 5', '1.0') 

 declare @a table (AppName varchar(255) primary key);
 insert into @a 
 select distinct AppName
   from @t 
  where OS = 'Windows 10' ;
 select * from @a;

 select t7.ComputerName
   from @t t7 
   left join @a a 
     on t7.AppName = a.AppName 
  where t7.OS = 'Windows 7'
  group by t7.ComputerName 
  having count(*) = count(a.AppName);
    • 0

relate perguntas