postgres嵌套查询并保持顺序

9jyewag0  于 2021-08-09  发布在  Java
关注(0)|答案(1)|浏览(410)

我有两个查询,想合并成一个。
查询1(伪代码):

recipe_titles = select recipe_title
             from recipe
             where nutrient_id in (4,5)
             group by recipe_title
             order by sum(nutrient_amount) desc

那么,我想用每一个 recipe_title 要选择整个配方行:
查询2(伪代码):

select * from recipe where recipe_title = recipe_titles[n].    (n is 1, then 2, then 3...)

如果第一个查询返回100 recipe_title ,我将不得不运行第二个查询100次,这是没有效率的。
有没有办法在一个查询中进行这两个选择? IN 因为订单丢失,所以不适用于我的案例:

select * from recipe where recipe_title in recipe_titles. (Not work)
hyrbngr7

hyrbngr71#

可以使用窗口函数。我推荐一个有条件的窗口 sum() ,然后可以用于筛选和排序:

select *
from (
    select 
        r.*,
        sum(nutrient_amount) 
            filter(where nutrient_id in (4,5)) 
            over(partition by recipe_title) as sum_nutrient_amount
    from recipe r
) t
where sum_nutrient_amount > 0
order by sum_nutrient_amount desc, recipe_title, nutrient_id

另一方面,如果你不需要过滤 recipe_title 任何一个都有
nutrient_id 4 或者 5 ,则不需要子查询:

select r.* 
from recipe r
order by 
    sum(nutrient_amount) 
        filter(where nutrient_id in (4,5)) 
        over(partition by recipe_title) desc,
    recipe_title, 
    nutrient_id

相关问题