IN 값 목록을 기준으로 주문

IN 값 목록을 기준으로 주문

Postgre에 간단한 SQL 쿼리가 있습니다.SQL 8.3은 많은 의견을 제공합니다.값의 정렬된 목록을 다음에 제공합니다.IN 니다합설에 합니다.WHERE절:

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

하는데, 는 내 " 은것댓반환니다합다같습니다내음과이는."와 같은 .1,2,3,4.

저는 과행을다목같정다니렬합이의 .IN 구성:(1,3,2,4).
어떻게 그것을 달성할 수 있을까요?

(Postgre에 소개된)로 꽤 쉽게 할 수 있습니다.SQL 8.2) 값(),().

구문은 다음과 같습니다.

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

Postgres 9.4 이상에서는 이것이 가장 간단하고 빠릅니다.

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

• WITH ORDINALITY Postgres 9.4에 도입되었습니다.

• 하위 쿼리가 필요하지 않고, 우리는 집합 반환 함수를 테이블처럼 직접 사용할 수 있습니다. (일명 "테이블 함수")

• 일부 클라이언트에서는 ARRAGE 생성자 대신 배열에 전달할 문자열 리터럴을 구현하는 것이 더 쉬울 수 있습니다.

• 위해 (할 열 이름을 하세요. " (의 "")로할 수 . 그러면 짧게 가입할 수 있습니다.USING결과에서 조인 열의 단일 인스턴스만 가져오는 절입니다.

• 모든 입력 유형에서 작동합니다.키 열이 유형인 경우text와 같은 것을 제공합니다.'{foo,bar,baz}'::text[].

자세한 설명:

• 요소 번호가 있는 PostgreSQL unnest()

찾기가 너무 어렵고 분산되어야 하기 때문에 mySQL에서는 훨씬 더 간단하게 수행할 수 있지만 다른 SQL에서는 작동하는지 모르겠습니다.

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

Postgres 9.4를 사용하면 이 작업을 조금 더 짧게 수행할 수 있습니다.

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;

또는 파생 테이블 없이 좀 더 간결하게:

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

각 값에 위치를 수동으로 할당/유지할 필요가 없습니다.

Postgres 9.6에서는 다음을 사용하여 이 작업을 수행할 수 있습니다.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는 값 목록을 한 번만 지정하면 되도록 사용됩니다.중요하지 않은 경우 다음과 같이 기록할 수 있습니다.

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

저는 이 방법이 더 좋다고 생각합니다.

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

또 은 Postgres 를 사용하는 것입니다.idx기능.

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

다음을 생성하는 것을 잊지 마십시오.idx여기에 설명된 바와 같이 먼저 기능합니다. http://wiki.postgresql.org/wiki/Array_Index

Postgresql에서:

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

이 문제를 좀 더 연구하면서 저는 다음과 같은 해결책을 발견했습니다.

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

그러나 이는 다소 장황하게 보이며 대규모 데이터셋에서 성능 문제가 발생할 수 있습니다.누가 이 문제들에 대해 논평할 수 있습니까?

이를 위해서는 주문에 대한 ID 매핑을 정의하는 추가 "ORDER" 테이블이 있어야 한다고 생각합니다(자신의 질문에 대한 응답을 효과적으로 수행). 그런 다음 정렬할 수 있는 선택 항목의 추가 열로 사용할 수 있습니다.

그런 식으로 데이터베이스에서 원하는 순서를 명시적으로 설명합니다.

sans SEQUENCE, 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

선보다 악을 더 좋아한다면,

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

여기에 작동하고 상수 테이블을 사용하는 또 다른 솔루션이 있습니다(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

하지만 다시 한번 말하지만 이것이 성과가 있는지 확신할 수 없습니다.

저는 이제 많은 답을 얻었습니다.누가 승자인지 알 수 있도록 투표와 댓글을 받을 수 있을까요?

모두 감사합니다 :-)

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;

[편집]

8.3에는 아직 unnest가 내장되어 있지 않지만 직접 unnest를 생성할 수 있습니다(모든 것의 아름다움*).

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;
$$;

이 기능은 모든 유형에서 작동할 수 있습니다.

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

시퀀스를 사용하는 버전보다 약간 개선되었습니다.

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)

여기서 [bbs]는 ids라는 필드가 있는 기본 테이블이고, ids는 comments.id 를 저장하는 배열입니다.

postgresql 9.6에서 전달됨

이미 말한 것에 대한 시각적인 인상을 받아 보겠습니다.예를 들어 다음과 같은 작업이 포함된 테이블이 있습니다.

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

또한 태스크 목록을 상태별로 정렬하려고 합니다.상태는 문자열 값 목록입니다.

(processing, pending,  completed, deleted)

이 방법은 각 상태 값에 정수를 지정하고 목록을 숫자로 정렬하는 것입니다.

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;

이는 다음으로 이어집니다.

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

크레딧 @user80168

저는 "그러지 마세요" 또는 "SQL은 그것을 잘하지 못합니다"라는 다른 모든 포스터에 동의합니다.주석의 일부 측면을 기준으로 정렬하려면 테이블 중 하나에 다른 정수 열을 추가하여 정렬 기준을 유지하고 해당 값을 기준으로 정렬합니다.예: "댓글별 주문"sort DESC "매번 다른 순서로 정렬하려면... 이 경우 SQL이 적합하지 않습니다.

언급URL : https://stackoverflow.com/questions/866465/order-by-the-in-value-list