Как упорядочить результат запроса в соответствии с порядком элементов массива?

Question

У меня есть этот запрос в Postgres 9.4:

select id from question where id = any(
    array_cat(
        ARRAY[0,579489,579482,579453,561983,561990,562083]::integer[], 
        (select array(
            select id from question where id not in 
                (0,579489,579482,579453,561983,561990,562083)
            and status in (1, -1) 
            and created_at > 1426131436 order by id desc offset 0 limit 10 )
        )::integer[]
    )
)

Возвращает:

   id
--------
 561983
 561990
 562083
 579453
 579482
 579489
 580541
 580542
 580543
 580544
 580545
 580546
 580547
 580548
 580549
 580550
(16 rows)

Но это не в правильном порядке. Мне нужен результат, упорядоченный в соответствии с результатом вложенного массива:

array_cat(
        ARRAY[0,579489,579482,579453,561983,561990,562083]::integer[], 
        (select array(
            select id from question where id not in 
                (0,579489,579482,579453,561983,561990,562083)
            and status in (1, -1) 
            and created_at > 1426131436 order by id desc offset 0 limit 10 )
        )::integer[]
    )

Как я могу это сделать?

Answer 1

Основы:

• PostgreSQL unnest () с номером элемента

Поскольку вы используете Postgres 9.4, вы можете использовать новый WITH ORDINALITY:

WITH t AS (
   SELECT *
   FROM   unnest('{0,579489,579482,579453,561983,561990,562083}'::int[])
                  WITH ORDINALITY AS t(id, rn)
   )
(
SELECT id
FROM   question
JOIN   t USING (id)
ORDER  BY t.rn
)
UNION ALL
(
SELECT id
FROM   question
LEFT   JOIN t USING (id)
WHERE  t.id IS NULL
AND    status IN (1, -1) 
AND    created_at > 1426131436
ORDER  BY id DESC
LIMIT  10
);

объяснять

1. Поскольку вы используете один и тот же массив дважды, я добавляю к запросу CTE, где вы предоставляете свой массив один раз. unnest() это сразу WITH ORDINALITY получить номера строк (rn) в соответствии с порядком элементов массива.

2. Вместо того, чтобы вставлять подзапрос в массив и преобразовывать его обратно, используйте его напрямую. Более дешевый. Порядок сортировки получен из id непосредственно.

3. Вместо исключения идентификаторов из данного массива с NOT IN (что может быть сложно со значениями NULL) LEFT JOIN / IS NULL:

   • Выберите строки, которых нет в другой таблице

4. Просто добавьте две части с UNION ALL, Скобки должны иметь отдельные ORDER BY на каждой ноге UNION ALL запрос:

   • Суммируйте результаты нескольких запросов и затем найдите топ-5 в SQL

5. JOIN в question в финале SELECT сейчас избыточно, я его убрал.

Answer 2

ORDER BY idx(your_array, your_element)

или же

ORDER BY your_array # your_element

INTArray

select id from question where id = any(
    array_cat(
        ARRAY[0,579489,579482,579453,561983,561990,562083]::integer[], 
        (select array(
            select id from question where id not in 
                (0,579489,579482,579453,561983,561990,562083) and status in (1, -1) 
                and created_at > 1426131436 order by id desc offset 0 limit 10 )
        )::integer[]
    ) 
) ORDER BY array_cat(
    ARRAY[0,579489,579482,579453,561983,561990,562083]::integer[], 
    (select array(
        select id from question where id not in 
            (0,579489,579482,579453,561983,561990,562083) and status in (1, -1) 
            and created_at > 1426131436 order by id desc offset 0 limit 10 )
    )::integer[]
) # id