Início / dba / Perguntas / 77990
Accepted
Nathan Long
Asked: 2014-09-30 13:20:27 +0800 CST

Liste valores de coluna distintos onde essas linhas compartilham outros valores de coluna

  • 772

Estou tentando escrever uma consulta para detectar dados possivelmente inválidos em uma tabela PostgreSQL. Temos uma tabela de nomes de cidades como esta:

# `city_names`
id    | name     |   language | dialect | city_id
------------------------------------------------
01    | London   |   A        | A1      | 1
02    | London   |   A        | A2      | 1
03    | London   |   B        | B1      | 2
04    | London   |   B        | B2      | 3

Em nosso domínio:

  • É bom que as linhas 01 e 02 mapeiem "Londres" para a cidade 1; os dialetos não são diferentes
  • Tudo bem que a linha 03 mapeia "Londres" para a cidade 2; nesse idioma, o nome pode se referir a uma cidade diferente
  • É suspeito que a linha 04 mapeie "Londres" para a cidade 3, porque já temos um mapeamento para a cidade 2 no mesmo idioma

Quero escrever uma consulta que selecione apenas as linhas 03 e 04 para que um humano possa decidir se uma delas aponta para a cidade errada.

Eu posso resolver esse problema processualmente, mas estou tendo problemas para fazer isso no SQL. Por exemplo, se eu GROUP BYidioma e nomeio, perco os city_idvalores das linhas individuais.

Basicamente, meu objetivo é: "Se houver mais de um city_id para o mesmo nome e idioma, liste esses city_ids."

Como posso fazer isso?

postgresql

1 respostas

  1. Best Answer

    Esta consulta servirá. O truque é usar COUNT(DISTINCT city_id):

    SQL Fiddle

    Configuração do esquema do PostgreSQL 8.3.20 :

    CREATE TABLE Table1
    ("id" int, "name" varchar(6), "language" varchar(1), "dialect" varchar(2), "city_id" int)
;

INSERT INTO Table1
    ("id", "name", "language", "dialect", "city_id")
VALUES
    (01, 'London', 'A', 'A1', 1),
    (02, 'London', 'A', 'A2', 1),
    (03, 'London', 'B', 'B1', 2),
    (04, 'London', 'B', 'B2', 3)
;

    Consulta 1 :

    select t.*, d.dups
FROM table1 t INNER JOIN
(
  select name, language, count(distinct city_id) as dups
  from table1
  group by name, language
  having count(distinct city_id) > 1
) d
ON t.name = d.name and t.language = d.language

    Resultados :

    | ID |   NAME | LANGUAGE | DIALECT | CITY_ID | DUPS |
|----|--------|----------|---------|---------|------|
|  3 | London |        B |      B1 |       2 |    2 |
|  4 | London |        B |      B2 |       3 |    2 |
    • 1

relate perguntas