SQL 子查询和排序？(订购者)

Sub queries and sorting? (ORDER BY)

提问by Nicholas Kreidberg

So my syntax is apparently correct in all three cases (PostgreSQL isn't grousing about anything) but the results come back in the same order with all three of these queries. Even stranger when I add/remove DESC from any of the following it has no impact either. Is it possible to sort results based on elements of a sub query or not?

所以我的语法在所有三种情况下显然都是正确的（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)

I am just not sure why the ORDER BY conditions are having no impact on the order of the results.

我只是不确定为什么 ORDER BY 条件对结果的顺序没有影响。

********* UPDATE:

********* 更新：

What I ended up doing was using the array column in my view (in this case articles_view) to do all my sorting. That way I do all my sorts on a "column" in the primary query and totally avoid having to use JOINS. The way the view is defined, all the columns matching a given pubid (primary key) in the people/status table (both have a 1->many) are stored in array columns in the view. My query with the sort looks like this:

我最终做的是在我的视图中使用数组列（在本例中为articles_view）来完成我的所有排序。这样我就可以在主查询中的“列”上进行所有排序，并且完全避免使用 JOINS。视图的定义方式是，在 people/status 表中与给定 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

The reason this works is because I always know that the first member of the array (in Postgres the first index is 1 rather than the usual 0), is the primary author (or primary status) which is what I need for sorting.

这样做的原因是因为我总是知道数组的第一个成员（在 Postgres 中，第一个索引是 1 而不是通常的 0），是主要作者（或主要状态），这是我进行排序所需要的。

采纳答案by Nicholas Kreidberg

What I ended up doing was using the array column in my view (in this case articles_view) to do all my sorting. That way I do all my sorts on a "column" in the primary query and totally avoid having to use JOINS. The way the view is defined, all the columns matching a given pubid (primary key) in the people/status table (both have a 1->many) are stored in array columns in the view. My query with the sort looks like this:

我最终做的是在我的视图中使用数组列（在本例中为articles_view）来完成我的所有排序。这样我就可以在主查询中的“列”上进行所有排序，并且完全避免使用 JOINS。视图的定义方式是，在 people/status 表中与给定 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

The reason this works is because I always know that the first member of the array (in Postgres the first index is 1 rather than the usual 0), is the primary author (or primary status) which is what I need for sorting.

这样做的原因是因为我总是知道数组的第一个成员（在 Postgres 中，第一个索引是 1 而不是通常的 0），是主要作者（或主要状态），这是我进行排序所需要的。

回答by Simon Fox

All the sub queries are doing is providing a set of results for the condition to check for the existence of spubid. You need to actually join to the status table and then use the columns in an order by clause on the outer query.

所有子查询所做的都是为条件提供一组结果，以检查 spubid 是否存在。您需要实际加入状态表，然后在外部查询的 order by 子句中使用列。

Something like:

就像是：

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

回答by VoteyDisciple

You're not ordering the outerquery; you're only ordering the innerquery. It's perfectly legal, but all you're doing with those inner results is comparing spubidagainst them, and it doesn't much matter what order you do that in.

您没有订购外部查询；您只是订购内部查询。这是完全合法的，但是您对这些内在结果所做的只是spubid与它们进行比较，并且您以什么顺序进行操作并不重要。

What you're looking for is a JOIN.

您正在寻找的是一个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

(You could rewrite the the other lookup as a join also, but for simplicity I left that one alone.)

（您也可以将另一个查找重写为连接，但为了简单起见，我单独留下了那个。）

回答by Tom Neyland

You are only sorting the data that is used by the IN statements. You need to sort your top level Select statement.

您只是对 IN 语句使用的数据进行排序。您需要对顶级 Select 语句进行排序。

Edit:

编辑：

And since the Select statements inside of the IN clauses are not contributing to the overall sorting of your results, you should remove the order by clauses from them, thus preventing the server from having to do unneeded processing.

而且由于 IN 子句中的 Select 语句对结果的整体排序没有贡献，您应该从它们中删除 order by 子句，从而防止服务器进行不必要的处理。