Início / dba / Perguntas / 217987
Accepted
TheDemonLord
Asked: 2018-09-19 20:59:35 +0800 CST

Problemas ao usar a função Max()

  • 772

Tenho 2 tabelas: Cliente e Faturamento, Cliente contém detalhes genéricos (nome, endereço, você entendeu) e Faturamento contém itens que são faturados.

A data final de cobrança do item é variável (algumas pessoas estão em um ciclo mensal, outras em 3 meses, 6 meses 1 ano etc.).

No Item de Cobrança o cliente pode ter diversos pacotes - alguns deles são pacotes que o marcam como Revendedor. Minha consulta SQL atual se parece com isso:

select customer.addresses_0_firstname AS AdminFName, 
       customer.addresses_0_lastname AS AdminLName, 
       customer.addresses_0_organisation AS CompanyName, 
       customer.addresses_0_address1 AS Address1, 
       customer.addresses_0_address2 AS Address2, 
       customer.addresses_0_city AS City, 
       customer.addresses_0_postcode AS Zip, 
       customer.addresses_0_country AS CountryID, 
       customer.addresses_0_email AS Email, 
       (CASE WHEN billing.lines_0_description like '%Package0%' 
             THEN 'False' 
             ELSE 'true' END) AS Reseller, 
       max(billing.end_date) 
from customer 
INNER JOIN billing on customer._id = billing.customer_$id 
where customer.name='example' 
  and (billing.lines_0_description like '%package1%' 
       or 
       billing.lines_0_description like '%package2%' 
       OR 
       billing.lines_0_description like'%package3%') 
group by customer.addresses_0_firstname, 
         customer.addresses_0_lastname, 
         customer.addresses_0_organisation, 
         customer.addresses_0_address1, 
         customer.addresses_0_address2, 
         customer.addresses_0_city, 
         customer.addresses_0_postcode, 
         customer.addresses_0_country, 
         customer.addresses_0_email, 
         billing.lines_0_description

Que produz o seguinte:

AdminFName  AdminLName  CompanyName Address1    Address2    City    Zip CountryID   Email           Reseller    (No column name)
Test User   Testing     123         test        Rd          test    0   Test        [email protected]   true        2012-03-23
Test User   Testing     123         test        Rd          test    0   Test        [email protected]   true        2012-01-23

Que é uma lista de todos os dados do usuário (que queremos), mas unidos a cada linha de cobrança, em vez de selecionar a data_final com o maior valor.

Eu suspeito que tenho que fazer uma seleção aninhada, mas não tenho certeza da lógica que preciso aqui.

Sugestões?

sql-server sql-server-2014

3 respostas

  1. Best Answer

    Você não pode obter um único valor MAX para end_date enquanto estiver agrupando em lines_0_description porque cada registro de cobrança terá, presumivelmente, uma descrição diferente produzindo um grupo distinto diferente e valores max end_date diferentes.

    Você pode usar um CTE para extrair o registro de cobrança mais recente por cliente e juntá-lo ao seu SELECT principal para produzir o conjunto de resultados desejado. Algo como abaixo deve funcionar (disclaimer: eu adivinhei alguns nomes de coluna, por exemplo, _id na tabela de cobrança, pois não conheço o esquema)

    ;WITH BillingRecords AS (
  SELECT MAX(_id) AS billing_id,
    customer_$id
  FROM billing
  WHERE (billing.lines_0_description like '%package1%' 
       or 
       billing.lines_0_description like '%package2%' 
       OR 
       billing.lines_0_description like'%package3%')
  GROUP BY customer_$id
)


  select customer.addresses_0_firstname AS AdminFName, 
       customer.addresses_0_lastname AS AdminLName, 
       customer.addresses_0_organisation AS CompanyName, 
       customer.addresses_0_address1 AS Address1, 
       customer.addresses_0_address2 AS Address2, 
       customer.addresses_0_city AS City, 
       customer.addresses_0_postcode AS Zip, 
       customer.addresses_0_country AS CountryID, 
       customer.addresses_0_email AS Email, 
       (CASE WHEN billing.lines_0_description like '%Package0%' 
             THEN 'False' 
             ELSE 'true' END) AS Reseller, 
       billing.end_date
from customer 
INNER JOIN BillingRecords br ON br.customer_$id = customer._id
INNER JOIN billing on billing._id = br.billing_id
where customer.name='example' 
  and (billing.lines_0_description like '%package1%' 
       or 
       billing.lines_0_description like '%package2%' 
       OR 
       billing.lines_0_description like'%package3%') 
group by customer.addresses_0_firstname, 
         customer.addresses_0_lastname, 
         customer.addresses_0_organisation, 
         customer.addresses_0_address1, 
         customer.addresses_0_address2, 
         customer.addresses_0_city, 
         customer.addresses_0_postcode, 
         customer.addresses_0_country, 
         customer.addresses_0_email
    • 2

  2. Você pode usar uma função de janela para isso. Usando uma expressão de tabela comum para fornecer uma ordem de classificação das datas e, em seguida, filtrando a ordem na instrução de seleção final.

    with Cte as (
    select ...,
           ROW_NUMBER() OVER (PARTITION BY billing.customer_$id ORDER BY billing.end_date DESC) AS Date_Rank
    From ...
)
select ...
from Cte
where Date_Rank = 1

    https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-2017

    Peço desculpas pela falta de formatação, este é meu primeiro post e estou com um pouco de pressa.

    • 0

  3. Geralmente eu prefiro o método CTE postado por JHackney , mas se você está tentando evitar uma reescrita (por enquanto), basta alterar:

    group by customer.addresses_0_firstname, 
     ...,
     billing.lines_0_description

    Para

    group by customer.addresses_0_firstname, 
     ...,
     (CASE WHEN billing.lines_0_description like '%Package0%' 
         THEN 'False' 
         ELSE 'true' END)

    O problema é que você deseja recolher todos esses valores diferentes em duas strings distintas, mas se você agrupar pela string original em vez da saída recolhida, terá várias linhas contribuindo com grupos, mas isso não será óbvio na saída. (Você verá o que quero dizer se você apenas produzir issolines_0_description coluna em vez da CASEexpressão em sua consulta existente.)

    • 0

relate perguntas