Início / dba / Perguntas / 205664
Accepted
lumo
Asked: 2018-05-03 23:01:38 +0800 CST

Consultando uma lista ordenada com cláusula OR ou IN

  • 772

Estou consultando itemsa partir de um banco de dados e exibi-lo para o usuário. (no meu exemplo vou usar contactsem table tContacts.

O usuário seleciona alguns itemsem uma ordem que ele decide (o usuário pode escolher aleatoriamente os itens, então nenhuma coluna pode ser especificada para ordenar).

No aplicativo eu tenho os id's daqueles itemsem um arquivo ordered list.

Atualmente eu consulto mais detalhes para esses objetos one-by-one.

É possível criar uma consulta com uma "lista ordenada", que retorna itemsna mesma ordem ?

algo como (sim, isso é feio - provavelmente também atingindo o tamanho máximo da consulta sql):

SELECT * FROM (
    --how to append this list...
    SELECT 0 as listIndex, * from tContacts where conIdContact = 13259;
    SELECT 1 as listIndex, * from tContacts where conIdContact = 12472;
    [...]
    SELECT 568 as listIndex, * from tContacts where conIdContact = 12422;
)
ORDER BY listIndex
  • OU: eu tenho que continuar consultandoone-by-one
  • OU: isso precisa de uma tabela temporária (inserir id e prioridade), depois consultar e, em seguida, soltar a tabela temporária novamente.
  • OU: consulte a batche reordene os itens no código do aplicativo (isso pode ser lento devido a listas possivelmente grandes)

nota: uma solução independente de banco de dados é preferível (mariadb/mysql, postgresql e h2 são considerados usados ​​em produção.

meu conceito de tabela temporária:

CREATE TEMPORARY TABLE temp_OrderedContacts(
    listIndex INT NOT NULL DEFAULT 0,
    contactId INT NOT NULL DEFAULT 0
);

INSERT INTO temp_OrderedContacts (listIndex, contactId)
VALUES (0,13259), (1,12472), (2,12422);

SELECT * FROM tContacts
JOIN temp_OrderedContacts ON (tContacts.conIdContact = temp_OrderedContacts.contactId)

DROP TABLE temp_OrderedContacts;
query order-by

1 respostas

  1. Best Answer

    Uma solução do Postgres seria colocar todos os IDs em um array e usar algo assim:

    select t.*, il.listindex
from contacts t
   join unnest(array[13259, 12472, ..., 12422]) with ordinality as il(id, listindex) 
     on t.conidcontact = il.id
order by il.listindex;

    (O acima é realmente o SQL ANSI padrão - mas, até onde sei, apenas o Postgres e o HSQLDB suportam isso)

    A matriz também pode ser passada como um parâmetro em uma instrução preparada (ou qualquer que seja o termo para isso na linguagem de programação que você está usando).

    Se você puder criar uma consulta dinâmica, outra opção seria usar um construtor de valor de linha:

    select t.*, il.listindex
from contacts t
   join ( 
      values 
        (1, 13259), 
        (2, 12472),  
        ..... 
        (568, 12422)
  ) as il(listindex, id) on t.conidcontact = il.id
order by il.listindex;

    (O acima é 100% padrão ANSI SQL)

    • 5

relate perguntas