Подзапросы и сортировка? (СОРТИРОВАТЬ ПО)

Таким образом, мой синтаксис, по-видимому, верен во всех трех случаях (PostgreSQL ни на что не ворчит), но результаты возвращаются в том же порядке для всех трех этих запросов. Еще более странно, когда я добавляю/удаляю DESC из любого из следующих, это также не влияет. Можно ли сортировать результаты на основе элементов подзапроса или нет?

Sort by affiliation
SELECT * FROM articles_view WHERE (1=1) 
AND spubid IN 
  (SELECT people.spubid FROM people WHERE (people.slast ilike 'doe') 
    GROUP BY people.spubid, people.slast, people.saffil) 
AND spubid IN 
  (SELECT status.spubid FROM status WHERE ((status.imonth >= 01 OR status.imonth IS NULL) AND status.iyear >= 2000) AND ((status.imonth <= 01 OR status.imonth IS NULL) AND status.iyear <= 2008) ORDER BY status.iyear, status.imonth)

Sort by last name, descending order
SELECT * FROM articles_view WHERE (1=1) 
AND spubid IN 
  (SELECT people.spubid FROM people WHERE (people.slast ilike 'doe') 
    GROUP BY people.spubid, people.slast, people.saffil ORDER BY people.slast DESC) 
AND spubid IN 
  (SELECT status.spubid FROM status WHERE ((status.imonth >= 01 OR status.imonth IS NULL) AND status.iyear >= 2000) AND ((status.imonth <= 01 OR status.imonth IS NULL) AND status.iyear <= 2008))

Sort by year/month descending order
SELECT * FROM articles_view WHERE (1=1) 
AND spubid IN 
  (SELECT people.spubid FROM people WHERE (people.slast ilike 'doe') 
    GROUP BY people.spubid, people.slast, people.saffil ) 
AND spubid IN 
  (SELECT status.spubid FROM status WHERE ((status.imonth >= 01 OR status.imonth IS NULL) AND status.iyear >= 2000) AND ((status.imonth <= 01 OR status.imonth IS NULL) AND status.iyear <= 2008) ORDER BY status.iyear, status.imonth DESC)

Я просто не уверен, почему условия ORDER BY не влияют на порядок результатов.

********* ОБНОВИТЬ:

В итоге я использовал столбец массива в моем представлении (в данном случае article_view) для всей моей сортировки. Таким образом, я делаю все свои сортировки по «столбцу» в основном запросе и полностью избегаю использования JOINS. Способ определения представления заключается в том, что все столбцы, соответствующие заданному pubid (первичному ключу) в таблице «люди/статус» (оба имеют 1->many), хранятся в столбцах массива в представлении. Мой запрос с сортировкой выглядит так:

SELECT * FROM articles_view WHERE 
  ((articles_view.skeywords_auto ilike '%ice%') OR (articles_view.skeywords_manual ilike '%ice%')) 
  ORDER BY (articles_view.authors[1]).slast

Причина, по которой это работает, заключается в том, что я всегда знаю, что первый член массива (в Postgres первый индекс равен 1, а не обычному 0) является основным автором (или основным статусом), что мне и нужно для сортировки.


postgresql sql sorting subquery sql-order-by
person Nicholas Kreidberg    schedule 19.08.2009    source источник

Ответы (4)


arrow_upward
3
arrow_downward

Все подзапросы предоставляют набор результатов для условия проверки существования spubid. Вам нужно фактически присоединиться к таблице состояния, а затем использовать столбцы в предложении order by во внешнем запросе.

Что-то типа:

SELECT * 
FROM articles_view
       INNER JOIN status ON articles_view.spubid = status.spubid
       INNER JOIN people ON articles_view.spubid = people.spubid
WHERE ((status.imonth >= 01 OR status.imonth IS NULL) AND status.iyear >= 2000)
       AND ((status.imonth <= 01 OR status.imonth IS NULL)
       AND status.iyear <= 2008 AND people.slast ilike 'doe')
ORDER BY status.iyear, status.imonth
person Simon Fox    schedule 19.08.2009
comment
Тьфу, вот чего я боялся, я действительно хочу избежать использования соединения (продукт станет слишком большим, поэтому вместо этого используйте подзапросы). - person Nicholas Kreidberg; 20.08.2009
comment
Используя внутреннее соединение, вы получите только соответствующие результаты, а оптимизатор запросов должен определить план, который удаляет все, что не удовлетворяет предложению where, до применения соединения. - person Simon Fox; 20.08.2009

arrow_upward
2
arrow_downward

Вы не заказываете внешний запрос; вы заказываете только внутренний запрос. Это совершенно законно, но все, что вы делаете с этими внутренними результатами, — это сравниваете spubid с ними, и не так уж важно, в каком порядке вы это делаете.

То, что вы ищете, это JOIN.

SELECT * 
FROM articles_view
INNER JOIN status ON (status.spubid = articles_view.spubid AND ((status.imonth >= 01 OR status.imonth IS NULL) AND status.iyear >= 2000) AND ((status.imonth <= 01 OR status.imonth IS NULL) AND status.iyear <= 2008))
WHERE spubid IN 
  (SELECT people.spubid FROM people WHERE (people.slast ilike 'doe') 
   GROUP BY people.spubid, people.slast, people.saffil ) 
ORDER BY status.iyear, status.imonth DESC

(Вы также можете переписать другой поиск как соединение, но для простоты я оставил его в покое.)

person VoteyDisciple    schedule 19.08.2009
comment
Поскольку я не знаю заранее, захочет ли пользователь сортировать по полю в таблице людей ИЛИ статуса, мне придется присоединиться к обоим. Проблема заключается в продукте, с которым я остался, просто нет необходимости включать столько данных в запрос, но похоже, что это мой единственный вариант, если я хочу разрешить широкий спектр вариантов сортировки. - person Nicholas Kreidberg; 20.08.2009
comment
Вы включаете такой объем данных в обработку запроса независимо от того, JOIN вы это делаете или нет. Если вас беспокоит просто возврат такого количества столбцов, это легко исправить: просто выполните SELECT articles_view.* вместо SELECT * - person VoteyDisciple; 20.08.2009

arrow_upward
1
arrow_downward

Вы сортируете только те данные, которые используются операторами IN. Вам нужно отсортировать оператор Select верхнего уровня.

Редактировать:

А поскольку операторы Select внутри предложений IN не влияют на общую сортировку ваших результатов, вы должны удалить из них предложения order by, тем самым предотвратив ненужную обработку сервером.

person Tom Neyland    schedule 19.08.2009

arrow_upward
0
arrow_downward

В итоге я использовал столбец массива в моем представлении (в данном случае article_view) для всей моей сортировки. Таким образом, я делаю все свои сортировки по «столбцу» в основном запросе и полностью избегаю использования JOINS. Способ определения представления заключается в том, что все столбцы, соответствующие заданному pubid (первичному ключу) в таблице «люди/статус» (оба имеют 1->many), хранятся в столбцах массива в представлении. Мой запрос с сортировкой выглядит так:

SELECT * FROM articles_view WHERE 
  ((articles_view.skeywords_auto ilike '%ice%') OR (articles_view.skeywords_manual ilike '%ice%')) 
  ORDER BY (articles_view.authors[1]).slast

Причина, по которой это работает, заключается в том, что я всегда знаю, что первый член массива (в Postgres первый индекс равен 1, а не обычному 0) является основным автором (или основным статусом), что мне и нужно для сортировки.

person Nicholas Kreidberg    schedule 27.08.2009