ZAMÓW WEDŁUG listy wartości IN

Mam proste zapytanie SQL w PostgreSQL 8.3, które przechwytuje kilka komentarzy. Podaję posortowaną listę wartości do INkonstrukcji w WHEREklauzuli:

SELECT * FROM comments WHERE (comments.id IN (1,3,2,4));

To zwraca komentarze w dowolnej kolejności, która w moim przypadku jest podobna do identyfikatorów 1,2,3,4.

Chcę wynikające wierszy posortowanych jak listy w INkonstrukcie: (1,3,2,4).
Jak to osiągnąć?

Możesz to zrobić dość łatwo za pomocą (wprowadzonych w PostgreSQL 8.2) VALUES (), ().

Składnia będzie następująca:

select c.*
from comments c
join (
  values
    (1,1),
    (3,2),
    (2,3),
    (4,4)
) as x (id, ordering) on c.id = x.id
order by x.ordering

Tylko dlatego, że jest tak trudny do znalezienia i trzeba go rozpowszechniać: w mySQL można to zrobić znacznie prościej , ale nie wiem, czy działa to w innym SQL.

SELECT * FROM `comments`
WHERE `comments`.`id` IN ('12','5','3','17')
ORDER BY FIELD(`comments`.`id`,'12','5','3','17')

W Postgres 9.4 lub nowszym jest to prawdopodobnie najprostsze i najszybsze :

SELECT c.*
FROM   comments c
JOIN   unnest('{1,3,2,4}'::int[]) WITH ORDINALITY t(id, ord) USING (id)
ORDER  BY t.ord;

• Używając nowego WITH ORDINALITY, wspomnianego już @a_horse .

• Nie potrzebujemy podzapytania, możemy użyć funkcji zwracającej set, jak tabeli.

• Literał łańcuchowy do przekazania w tablicy zamiast konstruktora ARRAY może być łatwiejszy do zaimplementowania w przypadku niektórych klientów.

Szczegółowe wyjaśnienie:

• PostgreSQL unnest () z numerem elementu

Myślę, że ten sposób jest lepszy:

SELECT * FROM "comments" WHERE ("comments"."id" IN (1,3,2,4))
    ORDER BY  id=1 DESC, id=3 DESC, id=2 DESC, id=4 DESC

Z Postgres 9.4 można to zrobić trochę krócej:

select c.*
from comments c
join (
  select *
  from unnest(array[43,47,42]) with ordinality
) as x (id, ordering) on c.id = x.id
order by x.ordering;

Lub nieco bardziej zwarty bez tabeli pochodnej:

select c.*
from comments c
  join unnest(array[43,47,42]) with ordinality as x (id, ordering) 
    on c.id = x.id
order by x.ordering

Eliminacja konieczności ręcznego przypisywania / utrzymywania pozycji dla każdej wartości.

W Postgres 9.6 można to zrobić za pomocą array_position():

with x (id_list) as (
  values (array[42,48,43])
)
select c.*
from comments c, x
where id = any (x.id_list)
order by array_position(x.id_list, c.id);

CTE jest używane, więc lista wartości musi być określona tylko raz. Jeśli to nie jest ważne, można to również zapisać jako:

select c.*
from comments c
where id in (42,48,43)
order by array_position(array[42,48,43], c.id);

Innym sposobem na zrobienie tego w Postgres byłoby użycie idxfunkcji.

SELECT *
FROM comments
ORDER BY idx(array[1,3,2,4], comments.id)

Nie zapomnij idxnajpierw utworzyć funkcji, jak opisano tutaj: http://wiki.postgresql.org/wiki/Array_Index

W Postgresql:

select *
from comments
where id in (1,3,2,4)
order by position(id::text in '1,3,2,4')

Poszukując tego trochę więcej, znalazłem takie rozwiązanie:

SELECT * FROM "comments" WHERE ("comments"."id" IN (1,3,2,4)) 
ORDER BY CASE "comments"."id"
WHEN 1 THEN 1
WHEN 3 THEN 2
WHEN 2 THEN 3
WHEN 4 THEN 4
END

Jednak wydaje się to raczej rozwlekłe i może powodować problemy z wydajnością w przypadku dużych zbiorów danych. Czy ktoś może wypowiedzieć się na temat tych kwestii?

Aby to zrobić, myślę, że prawdopodobnie powinieneś mieć dodatkową tabelę „ZAMÓWIENIE”, która definiuje mapowanie identyfikatorów do zamówienia (skutecznie robiąc to, co mówi twoja odpowiedź na własne pytanie), której możesz następnie użyć jako dodatkowej kolumny w wybranym możesz następnie posortować.

W ten sposób wyraźnie określasz pożądaną kolejność w bazie danych, gdzie powinna się znajdować.

sans SEQUENCE, działa tylko na 8.4:

select * from comments c
join 
(
    select id, row_number() over() as id_sorter  
    from (select unnest(ARRAY[1,3,2,4]) as id) as y
) x on x.id = c.id
order by x.id_sorter

SELECT * FROM "comments" JOIN (
  SELECT 1 as "id",1 as "order" UNION ALL 
  SELECT 3,2 UNION ALL SELECT 2,3 UNION ALL SELECT 4,4
) j ON "comments"."id" = j."id" ORDER BY j.ORDER

lub jeśli wolisz zło od dobra:

SELECT * FROM "comments" WHERE ("comments"."id" IN (1,3,2,4))
ORDER BY POSITION(','+"comments"."id"+',' IN ',1,3,2,4,')

A oto inne rozwiązanie, które działa i wykorzystuje stałą tabelę ( http://www.postgresql.org/docs/8.3/interactive/sql-values.html ):

SELECT * FROM comments AS c,
(VALUES (1,1),(3,2),(2,3),(4,4) ) AS t (ord_id,ord)
WHERE (c.id IN (1,3,2,4)) AND (c.id = t.ord_id)
ORDER BY ord

Ale znowu nie jestem pewien, czy to działa.

Mam teraz kilka odpowiedzi. Czy mogę uzyskać głos i komentarze, żeby wiedzieć, kto wygrał!

Dziękuje wszystkim :-)

create sequence serial start 1;

select * from comments c
join (select unnest(ARRAY[1,3,2,4]) as id, nextval('serial') as id_sorter) x
on x.id = c.id
order by x.id_sorter;

drop sequence serial;

[EDYTOWAĆ]

unnest nie jest jeszcze wbudowany w 8.3, ale możesz go stworzyć samodzielnie (piękno każdego *):

create function unnest(anyarray) returns setof anyelement
language sql as
$$
    select $1[i] from generate_series(array_lower($1,1),array_upper($1,1)) i;
$$;

ta funkcja może działać w każdym typie:

select unnest(array['John','Paul','George','Ringo']) as beatle
select unnest(array[1,3,2,4]) as id

Nieznaczne ulepszenie w stosunku do wersji używającej sekwencji, jak myślę:

CREATE OR REPLACE FUNCTION in_sort(anyarray, out id anyelement, out ordinal int)
LANGUAGE SQL AS
$$
    SELECT $1[i], i FROM generate_series(array_lower($1,1),array_upper($1,1)) i;
$$;

SELECT 
    * 
FROM 
    comments c
    INNER JOIN (SELECT * FROM in_sort(ARRAY[1,3,2,4])) AS in_sort
        USING (id)
ORDER BY in_sort.ordinal;

select * from comments where comments.id in 
(select unnest(ids) from bbs where id=19795) 
order by array_position((select ids from bbs where id=19795),comments.id)

tutaj [bbs] jest główną tabelą zawierającą pole o nazwie ids, a ids to tablica przechowująca komentarze.id.

przekazano w postgresql 9.6

Zobaczmy, co zostało już powiedziane. Na przykład masz stół z kilkoma zadaniami:

SELECT a.id,a.status,a.description FROM minicloud_tasks as a ORDER BY random();

 id |   status   |   description    
----+------------+------------------
  4 | processing | work on postgres
  6 | deleted    | need some rest
  3 | pending    | garden party
  5 | completed  | work on html

I chcesz uporządkować listę zadań według statusu. Status to lista wartości ciągów:

(processing, pending,  completed, deleted)

Sztuczka polega na tym, aby nadać każdej wartości statusu interger i uporządkować listę numeryczną:

SELECT a.id,a.status,a.description FROM minicloud_tasks AS a
  JOIN (
    VALUES ('processing', 1), ('pending', 2), ('completed', 3), ('deleted', 4)
  ) AS b (status, id) ON (a.status = b.status)
  ORDER BY b.id ASC;

Który prowadzi do:

 id |   status   |   description    
----+------------+------------------
  4 | processing | work on postgres
  3 | pending    | garden party
  5 | completed  | work on html
  6 | deleted    | need some rest

Kredyt @ user80168

Zgadzam się ze wszystkimi innymi plakatami, które mówią „nie rób tego” lub „SQL nie jest w tym dobry”. Jeśli chcesz sortować według jakiegoś aspektu komentarzy, dodaj kolejną kolumnę liczb całkowitych do jednej z tabel, aby przechowywać kryteria sortowania i sortować według tej wartości. np. "ORDER BY comments.sort DESC" Jeśli chcesz sortować je w innej kolejności za każdym razem, ... SQL w tym przypadku nie będzie dla Ciebie.