Pensé que una vez que CASE encontrara una coincidencia, se rompería y devolvería la primera coincidencia. Sin embargo, obtengo todas las coincidencias de la declaración del caso. p.ej.

select distinct PERSON, 
                LOCATION, 
                (case
                  when LOCATION = 'CA' and PHONE is not null
                    then PHONE
                  when LOCATION = 'NY' and PHONE is not null
                    then PHONE
                  when LOCATION = 'FL' and PHONE is not null
                    then PHONE
                  when LOCATION = 'MA' and PHONE is not null
                    then PHONE
                  else '---'
                end)
from DIRECTORY
where LOCATION in
    ( 'CA', 'NY', 'FL', 'MA' );

Dado que la PERSONA puede tener números de teléfono en cada estado, lo que quiero es el primer número de teléfono encontrado, básicamente "clasificado" por el orden de los estados. Lo que obtengo es todos los números de teléfono que se encuentran.

Gracias...

0
user1628169 24 abr. 2017 a las 19:18

3 respuestas

La mejor respuesta

Puede asignar un rango a cada estado en subconsulta, y luego retener el registro de clasificación más alto para cada persona:

WITH cte1 AS (
    SELECT
        PERSON, 
        LOCATION,
        PHONE,
        CASE WHEN LOCATION = 'CA' AND PHONE IS NOT NULL THEN 1
             WHEN LOCATION = 'NY' AND PHONE IS NOT NULL THEN 2
             WHEN LOCATION = 'FL' AND PHONE IS NOT NULL THEN 3
             WHEN LOCATION = 'MA' AND PHONE IS NOT NULL THEN 4
             ELSE 5        -- the NULL case
        END AS LOCATION_RANK
    FROM DIRECTORY
    WHERE LOCATION IN ('CA', 'NY', 'FL', 'MA')
),
cte2 AS (
    SELECT t.*,
           ROW_NUMBER() OVER (PARTITION BY t.PERSON ORDER BY t.LOCATION_RANK) rn
    FROM cte1 t
)
SELECT
    t.PERSON,
    t.LOCATION,
    COALESCE(t.PHONE, '---')
FROM cte2 t
WHERE t.rn = 1
2
Tim Biegeleisen 24 abr. 2017 a las 16:29
select distinct PERSON, 
                LOCATION, 
                (case
                  when LOCATION = 'CA' and PHONE is not null 
                    then PHONE
                    else case
                  when LOCATION = 'NY' and PHONE is not null
                    then PHONE
                    else case
                  when LOCATION = 'FL' and PHONE is not null
                    then PHONE
                    else case
                  when LOCATION = 'MA' and PHONE is not null
                    then PHONE
                  else '---'
                end end end end)
from DIRECTORY
where LOCATION in
    ( 'CA', 'NY', 'FL', 'MA' );
0
Pika the Wizard of the Whales 22 mar. 2019 a las 04:15

El enfoque CTE de @ TimBielgeleisen puede ser más fácil de seguir y mantener, pero podría hacer lo mismo sin ningún CTE, utilizando keep dense_rank, aunque debe repetir la expresión de caso:

select person,
  min(location) keep (dense_rank first order by
    case when phone is null then 2 else 1 end,
    case location when 'CA' then 1 when 'NY' then 2 when 'FL' then 3
      when 'MA' then 4 end) as location,
  coalesce(min(phone) keep (dense_rank first order by
    case when phone is null then 2 else 1 end,
    case location when 'CA' then 1 when 'NY' then 2 when 'FL' then 3
      when 'MA' then 4 end), '---') as phone
from directory
where location in ('CA', 'NY', 'FL', 'MA')
group by person;

O puede mover las expresiones de caso a una sola vista CTE o en línea para hacerlas más fáciles de mantener:

select person,
  min(location) keep (dense_rank first order by phone_rank, location_rank) as location,
  coalesce(min(phone) keep (dense_rank first order by phone_rank, location_rank),
    '---') as phone
from (
  select person, location, phone,
    case location when 'CA' then 1 when 'NY' then 2 when 'FL' then 3
      when 'MA' then 4 end as location_rank,
    case when phone is null then 2 else 1 end as phone_rank
  from directory
  where location in ('CA', 'NY', 'FL', 'MA')
)
group by person;

Todavía está tocando la mesa una vez con cualquiera de estos enfoques, la clasificación se maneja de manera un poco diferente, por lo que no esperaría una diferencia de rendimiento (pero siempre es mejor verificar con sus datos reales, por supuesto).

0
Alex Poole 24 abr. 2017 a las 16:52