postgresql 子查询和排序?(ORDER BY)

kuarbcqp  于 2023-04-11  发布在  PostgreSQL
关注(0)|答案(4)|浏览(269)

所以我的语法在这三种情况下显然都是正确的(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条件对结果的顺序没有影响。

x0fgdtte

x0fgdtte1#

所有子查询所做的就是为条件提供一组结果,以检查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
kkbh8khc

kkbh8khc2#

您没有对 outer 查询进行排序;你只是对 inner 查询进行了排序,这是完全法律的的,但是你对那些内部结果所做的只是将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

(You我也可以将另一个查找重写为一个连接,但为了简单起见,我不去管它。)

2lpgd968

2lpgd9683#

您只对IN语句使用的数据进行排序。您需要对顶级Select语句进行排序。
编辑:
由于IN子句中的Select语句对结果的总体排序没有贡献,因此应该从它们中删除order by子句,从而防止服务器进行不必要的处理。

umuewwlo

umuewwlo4#

我最后做的是在我的视图中使用数组列(在本例中为articles_view)来完成所有排序。这样,我在主查询中的“列”上完成所有排序,完全避免了使用JOINS。视图的定义方式,匹配给定pubid的所有列(主键)在people/status表(都有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)是主要作者(或主要状态),这是我排序所需要的。

相关问题