postgres嵌套查询

alen0pnh 于 2021-08-09 发布在 Java

关注(0)|答案(1)|浏览(238)

我被一个嵌套的数据库查询卡住了。我能得到一些帮助吗？
我的简单表如下所示：
餐桌：

+----+----------+-------------------------------+
| ID | NAME     | nutrientName  | NutrientAmount
+----+----------+---------------+---------------+
        food1       calcium            200
        food1       magnesium          300
        food1       phosphorus         400
        food2       calcium            220
        food2       magnesium          320
        food2       phosphorus         430
        food3       calcium            230
     .............

我用这个获得了前15个营养量：

select NAME from food_table
    where nutrientName in ('calcium','magnesium')
        group by NAME
     order by sum(nutrient_amount) desc
limit 15;

目前，我只有名字。但是，我想以同样的顺序得到食物的全部营养。如果我再做一次选择，订单就会丢失。有没有办法得到同样顺序的食物记录？
编辑：我创建了一个小提琴：
https://www.db-fiddle.com/f/2hkvzpbtxkrnanpdjt35im/3

sql postgresql

来源：https://stackoverflow.com/questions/61861456/postgres-nested-query-for-single-table

1条答案

按热度按时间

dsekswqp1#

一种方法是使用窗口函数：

select ft.*
from (select ft.*,
             row_number() over (partition by name order by nutrient_amount desc) as seqnum
      from food_table ft
      where nutrientName in ('calcium','magnesium')
     ) ft
where seqnum = 1
order by nutrient_amount desc;

以上返回的营养素的最高数额为一个单一的营养素。如果两者都要，那么：

select ft.*
from (select ft.*,
             sum(nutrient_amount) over (partition by name order by nutrient_amount desc) as sum_nutrient_amount
      from food_table ft
      where nutrientName in ('calcium', 'magnesium')
     ) ft
order by sum_nutrient_amount desc
limit 15;

或者，如果您只是想要数量，那么条件聚合可能就是您想要的：

select name,
       sum(case when nutrientName = 'calcium' then nutrient_amount else 0 end) as ca,
       sum(case when nutrientName = 'magnesium' then nutrient_amount else 0 end) as mg
from food_table
group by name
order by (ca + mg) desc
limit 15;

赞(0）回复(0）举报 2021-08-09

我来回答

postgres嵌套查询

1条答案

相关问题

热门标签

最新问答