PostgreSQL中,可以使用一个查询的结果进行排序。

huangapple go评论55阅读模式
英文:

postgresql order by from result of other query

问题

这是我的第一个查询:

SELECT 
    ss.id AS ss_number
    FROM sales_sourcing ss
    WHERE ss.state NOT IN ('draft','cancel')
    AND ss.source_type = 'import'

这是我的第二个查询:

SELECT write_date FROM sales_sourcing_chat ORDER BY create_date DESC

两个查询之间的关系是 ss.id = chat_id。

我尝试的是根据第二个查询中最新 write_date 的顺序对第一个查询进行排序。

英文:

I have this two query.This is my first query:

SELECT 
    ss.id ss_number
    FROM sales_sourcing ss
    WHERE ss.state NOT IN ('draft','cancel')
    AND ss.source_type = 'import'

This is my seccond query:

SELECT write_date FROM sales_sourcing_chat ORDER BY create_date DESC

The relation is that the ss.id = chat_id<br>
What I'm trying to do is to order the first query base on the order of the latest write_date from second query.

答案1

得分: 1

根据 Arkena 的建议,看起来您想要加入它们:

SELECT ss.id ss_number
FROM sales_sourcing ss join sales_sourcing_chat ssc 
                        on  ss.id = ssc.chat_id
WHERE ss.state NOT IN ('draft','cancel')
AND   ss.source_type = 'import'
ORDER BY ssc.create_date desc, 
         ssc.write_date  desc;

您可能需要澄清这是否是您想要的顺序。毕竟,您试图按 write_date 值排序,这些值本身已经按 create_date 排序。

英文:

As suggested by Arkhena, it looks like you want to join them

SELECT ss.id ss_number
FROM sales_sourcing ss join sales_sourcing_chat ssc 
                        on  ss.id = ssc.chat_id
WHERE ss.state NOT IN (&#39;draft&#39;,&#39;cancel&#39;)
AND   ss.source_type = &#39;import&#39;
ORDER BY ssc.create_date desc, 
         ssc.write_date  desc;

You might want to clarify if that's the order you aimed for. After all, you're trying to order by write_date values that are themselves ordered by create_date.

huangapple
  • 本文由 发表于 2023年2月16日 14:33:57
  • 转载请务必保留本文链接:https://go.coder-hub.com/75468613.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定