postgresql 如何重写不带子查询的查询

ssgvzors 于 2023-01-25 发布在 PostgreSQL

关注(0)|答案(2)|浏览(173)

我有这个疑问

select 
    qt.id as id, 
    ev.id as event_id, 
    res.allocation_date as date 
from quota qt 
left join result res on qt.id=res.quota_fk 
and (res.allocation_date=(select max(res2.allocation_date) from result res2 where res2.quota_fk=qt.id)) 
left join event ev on qt.id=ev.quota_fk

此查询正确打印id和event_id，但不打印max date（打印空值）。
是否可以重写此查询，使其在查询的外部选择部分引用max（res2.allocation_date），而无需通过在外部选择部分添加子查询来重写查询本身？
我的意思是，我想选择max(res2.allocation_date)，但不想以下面的方式重写查询。
(adds select子句中的子查询，[Hibernate 5.x不允许在select中使用子查询]）

select 
    qt.id as id, 
    ev.id as event_id, 
    (select max(res3.allocation_date) from result res3 where res3.quota_fk=qt.id) as date 
from quota qt 
left join result res on qt.id=res.quota_fk 
and (res.allocation_date=(select max(res2.allocation_date) from result res2 where res2.quota_fk=qt.id)) 
left join event ev on qt.id=ev.quota_fk

还有
(adds查询末尾的where子句[似乎不正确。效率低吗？]）

select 
    qt.id as id, 
    ev.id as event_id, 
    res.allocation_date as date 
from quota qt 
left join result res on qt.id=res.quota_fk 
and (res.allocation_date=(select max(res2.allocation_date) from result res2 where 
res2.quota_fk=qt.id)) 
left join event ev on qt.id=ev.quota_fk
where (( res.allocation_date = (select max(allocation_date) from result where quota_fk = qt.id

postgresql

来源：https://stackoverflow.com/questions/75162206/how-to-rewrite-a-query-without-a-subquery

2条答案

按热度按时间

xxb16uws1#

像这样的东西？

select 
    qt.id as id, 
    ev.id as event_id, 
    coalesce( res.allocation_date, max.allocation_date ) as date 
from 
    quota qt 
    left join 
    event ev on ev.quota_fk = qt.id
    left join 
    (
        select quota_fk, max(allocation_date) as allocation_date
        from result
        group by quota_fk
    ) res on res.quota_fk = qt
    cross join
    (
        select max(allocation_date) as allocation_date 
        from result
    ) max

赞(0）回复(0）举报 2023-01-25

eqqqjvef2#

您只需要每个配额分配日期最早的那些行：

with data as (
    select 
        qt.id as id, ev.id as event_id, res.allocation_date as date,
        dense_rank() over (partition by qt.id order by res.allocation_date desc) rn 
    from quota qt
        left join result res on qt.id = res.quota_fk 
        left join event ev   on qt.id = ev.quota_fk
)
select * from data where rn = 1;

赞(0）回复(0）举报 2023-01-25

我来回答

postgresql 如何重写不带子查询的查询

2条答案

相关问题

热门标签

最新问答