sql - 如何从具有相同值的两个不同列中选择一行

Question

嗨，我只需要为这张表选择一行。

第 1 行 = 反向（第 4 行）

我的代码：

    select ROW_NUMBER() OVER() ID, c.localidad, acalle,bcalle
    from (
      SELECT a.localidad, a.calle acalle, b.calle bcalle
      FROM (
        SELECT LOCALIDAD, CALLE 
        FROM TEST.INICIAL
        WHERE CALLE IS NOT NULL
        GROUP BY 1,2
      ) A
      INNER JOIN (
        SELECT LOCALIDAD, CALLE 
        FROM TEST.INICIAL
        WHERE CALLE IS NOT NULL
        GROUP BY 1,2
      ) B ON A.LOCALIDAD = B.LOCALIDAD 
      WHERE SIMILARITY(A.CALLE ,B.CALLE) > 0.45
         AND A.CALLE <> B.CALLE
    ) c
    where acalle not like '%NORTE' 
      and acalle not like '%SUR' 
      and acalle not like '%ESTE' 
      and acalle not like '%OESTE' 
      AND Bcalle not like '%NORTE' 
      and Bcalle not like '%SUR' 
      and Bcalle not like '%ESTE' 
      and Bcalle not like '%OESTE' 
      AND ACALLE NOT LIKE 'PJE%' 
      AND BCALLE NOT LIKE 'PJE%'
      and acalle not like '%BIS'
      and bcalle not like '%BIS' 
      and acalle not like '%CALLE%' 
      AND BCALLE NOT LIKE '%CALLE%'
    order by 1,2,3

标签： sqlpostgresqlselect

Answer 1

使用 aCASE确保列值的顺序相同（不管它们的顺序如何，只是从一行到下一行是一致的），构建一个数组[col1, col2], 并以此为基础SELECT DISTINCT。

testdb=# with orig_output as (
select 1::bigint as id, 'ADROGUE' as localidad, 'JORGE' as acalle, 'JORGE DE KAY' as bcalle UNION ALL
select 2, 'ADROGUE', 'PLAZA ESTABAN ADROGUE', 'ESTEBAN ADROGUE' UNION ALL
select 3, 'ADROGUE', 'ESTEBAN ADROGUE', 'PLAZA ESTABAN ADROGUE' UNION ALL
select 4, 'ADROGUE', 'JORGE DE KAY', 'JORGE'),
make_columns_be_in_same_order AS (
SELECT id, localidad, acalle, bcalle,
  CASE WHEN acalle>bcalle
    THEN ARRAY[acalle, bcalle]
    ELSE ARRAY[bcalle, acalle]
  END AS acalle_and_bcalle
FROM orig_output
)
SELECT DISTINCT ON (acalle_and_bcalle)
id, localidad, acalle, bcalle
FROM make_columns_be_in_same_order;
 id | localidad |        acalle         |     bcalle      
----+-----------+-----------------------+-----------------
  1 | ADROGUE   | JORGE                 | JORGE DE KAY
  2 | ADROGUE   | PLAZA ESTABAN ADROGUE | ESTEBAN ADROGUE
(2 rows)