none
como retornar esses 2 resultados via select RRS feed

  • Pergunta

  • Olá amigos, estou quebrando a cabeça para montar dois selects referentes a duas tabelas de uma banco de dados. Vou passar os scripts do create de cada tabela e imagens de ambas populadas e então direi os selects que quero elaborar:

    create table Pessoa 
    (
    id int identity,
    nome varchar(50),
    sexo char(1),
    constraint pk_id primary key(id)
    )
    
    create table Lancamento
    (
    id int identity,
    natureza char(1), -- C para crédito, D para débito
    datalancamento datetime,
    idpessoa int,
    idpessoalancamento int,
    valor decimal,
    constraint pk_lanc_id primary key(id),
    constraint fk_idpessoa foreign key(idpessoa) references Pessoa(id),
    constraint fk_idpessoalancamento foreign key(idpessoalancamento) references Pessoa(id),
    )

    Os selects que eu gostaria de elaborar são os seguintes:

    Fazer uma consulta que retorne todas as pessoas que não possuem nenhum lançamento.

    Fazer uma consulta que retorne os lançamentos de uma determinada pessoa, mostrando o nome de quem fez o lançamento e o nome de quem recebeu.

    Podem me ajudar com isso ? tentei bastante mas não obtive sucesso nos resultados.

    Obrigado.

    quinta-feira, 10 de outubro de 2019 03:46

Respostas

  • (...) Vou passar os scripts do create de cada tabela

    Mas qual a diferença entre as colunas IDPESSOA e IDPESSOALANCAMENTO da tabela LANCAMENTO? Isto é, a natureza do lançamento (débito ou crédito) se refere à primeira (IDPESSOA) ou à segunda (IDPESSOALANCAMENTO) pessoa?

    Há lançamento de contrapartida? Por exemplo, se houve lançamento de crédito de A para B, existe lançamento de débito de B para A?

      

    Fazer uma consulta que retorne todas as pessoas que não possuem nenhum lançamento.

    Eis uma abordagem:

    -- código #1
    SELECT P.nome
      from PESSOA as P
      where not exists (SELECT * from LANCAMENTO as L where L.idpessoa = P.id)
            and not exists (SELECT * from LANCAMENTO as L where L.idpessoalancamento = P.id);

    Não testei; pode conter erro(s).

    Também é possível resolver com NOT IN, mas é uma abordagem ruim, ineficiente para este caso.

      

    Fazer uma consulta que retorne os lançamentos de uma determinada pessoa, mostrando o nome de quem fez o lançamento e o nome de quem recebeu

    Eis uma forma, onde na variável @pessoa deve ser informado o código da pessoa:

    -- código #2
    declare @pessoa int;
    set @pessoa= ___;
    
    with
    L_PESSOA as (
    SELECT *
      from LANCAMENTO
      where L.idpessoa = @pessoa
    union all
    SELECT *
      from LANCAMENTO
      where L.idpessoalancamento = @pessoa
    )
    SELECT L.id, L.natureza, L.datalancamento, L.valor,
           P1.nome as [nome 1], P2.nome as [nome 2]
      from L_PESSOA as L
           inner join PESSOA as P1 on P1.id = L.idpessoa
           inner join PESSOA as P2 on P2.id = L.idpessoalancamento; 

    Não testei; pode conter erro(s).

       

    Eis outra opção, mas com predicado non sargable:

    -- código #3
    declare @pessoa int;
    set @pessoa= ___;
    
    SELECT L.id, L.natureza, L.datalancamento, L.valor,
           P1.nome as [nome pessoa], P2.nome as [nome pessoa lançamento]
      from LANCAMENTO as L
           inner join PESSOA as P1 on P1.id = L.idpessoa
           inner join PESSOA as P2 on P2.id = L.idpessoalancamento
      where L.idpessoa = @pessoa 
            or L.idpessoalancamento = @pessoa;

    Não testei; pode conter erro(s).

    No artigo “Construindo códigos T-SQL eficientes” você encontra detalhes sobre como construir códigos eficientes através do uso de predicados sargable.

    Se não houver índices pelas colunas idpessoa e idpessoalancamento o código #3 é mais eficiente (mesmo sendo non sargable), pois envolve uma única leitura completa na tabela LANCAMENTO.



    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Sugerido como Resposta José Diz segunda-feira, 14 de outubro de 2019 13:44
    • Marcado como Resposta IgorFKModerator segunda-feira, 14 de outubro de 2019 18:01
    • Editado José Diz segunda-feira, 14 de outubro de 2019 23:15
    quinta-feira, 10 de outubro de 2019 09:19

Todas as Respostas

  • (...) Vou passar os scripts do create de cada tabela

    Mas qual a diferença entre as colunas IDPESSOA e IDPESSOALANCAMENTO da tabela LANCAMENTO? Isto é, a natureza do lançamento (débito ou crédito) se refere à primeira (IDPESSOA) ou à segunda (IDPESSOALANCAMENTO) pessoa?

    Há lançamento de contrapartida? Por exemplo, se houve lançamento de crédito de A para B, existe lançamento de débito de B para A?

      

    Fazer uma consulta que retorne todas as pessoas que não possuem nenhum lançamento.

    Eis uma abordagem:

    -- código #1
    SELECT P.nome
      from PESSOA as P
      where not exists (SELECT * from LANCAMENTO as L where L.idpessoa = P.id)
            and not exists (SELECT * from LANCAMENTO as L where L.idpessoalancamento = P.id);

    Não testei; pode conter erro(s).

    Também é possível resolver com NOT IN, mas é uma abordagem ruim, ineficiente para este caso.

      

    Fazer uma consulta que retorne os lançamentos de uma determinada pessoa, mostrando o nome de quem fez o lançamento e o nome de quem recebeu

    Eis uma forma, onde na variável @pessoa deve ser informado o código da pessoa:

    -- código #2
    declare @pessoa int;
    set @pessoa= ___;
    
    with
    L_PESSOA as (
    SELECT *
      from LANCAMENTO
      where L.idpessoa = @pessoa
    union all
    SELECT *
      from LANCAMENTO
      where L.idpessoalancamento = @pessoa
    )
    SELECT L.id, L.natureza, L.datalancamento, L.valor,
           P1.nome as [nome 1], P2.nome as [nome 2]
      from L_PESSOA as L
           inner join PESSOA as P1 on P1.id = L.idpessoa
           inner join PESSOA as P2 on P2.id = L.idpessoalancamento; 

    Não testei; pode conter erro(s).

       

    Eis outra opção, mas com predicado non sargable:

    -- código #3
    declare @pessoa int;
    set @pessoa= ___;
    
    SELECT L.id, L.natureza, L.datalancamento, L.valor,
           P1.nome as [nome pessoa], P2.nome as [nome pessoa lançamento]
      from LANCAMENTO as L
           inner join PESSOA as P1 on P1.id = L.idpessoa
           inner join PESSOA as P2 on P2.id = L.idpessoalancamento
      where L.idpessoa = @pessoa 
            or L.idpessoalancamento = @pessoa;

    Não testei; pode conter erro(s).

    No artigo “Construindo códigos T-SQL eficientes” você encontra detalhes sobre como construir códigos eficientes através do uso de predicados sargable.

    Se não houver índices pelas colunas idpessoa e idpessoalancamento o código #3 é mais eficiente (mesmo sendo non sargable), pois envolve uma única leitura completa na tabela LANCAMENTO.



    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Sugerido como Resposta José Diz segunda-feira, 14 de outubro de 2019 13:44
    • Marcado como Resposta IgorFKModerator segunda-feira, 14 de outubro de 2019 18:01
    • Editado José Diz segunda-feira, 14 de outubro de 2019 23:15
    quinta-feira, 10 de outubro de 2019 09:19
  • Funcionou perfeitamente. Obrigado.
    segunda-feira, 14 de outubro de 2019 04:44