sql-从一对多关系中选择最大和最小行

ogq8wdun 于 2021-07-24 发布在 Java

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

我有三个表以一对多的关系连接起来：a+b->一对多，b+c->一对多。对于每个条目a，我想得到它的所有b条目和它们的两个连接的c条目 max(C.created) 以及 min(C.created) 答：

+-----+
| id  | 
+-----+
|0.   |
+-----+

第二：

+---------------+
| id   | A_id  |
+---------------+
|1.    | 0.    |
|2.    | 0.    |
+---------------+

c类：

+--------------------------+
| id  | B_id   | created   |
+--------------------------+
|3    | 1      | 2010
|4    | 1      | 2015
|5    | 1      | 2020
|6    | 2      | 2011
|7    | 2      | 2015
|8    | 2      | 2014
+--------------------------+

结果：

+--------------------------+
| A_id  | C_id   | created|
+--------------------------+
|0       | 3      | 2010
|0       | 5      | 2020
|0       | 6      | 2011
|0       | 7      | 2015
+--------------------------+

这就是我失败的尝试的样子，我只是不能让它像我想的那样工作：

select A.id, C.id, C.created
from A
join B on (A.id = B.A_id)
join (select *
      from C
            where C.created = (select min(created) from C) or
                  C.created = (select max(created) from C)
) as foo
on B.id = foo.B_id

sql postgresql one-to-many

来源：https://stackoverflow.com/questions/62995158/sql-select-max-and-min-rows-from-one-to-many-relationship

2条答案

按热度按时间

2lpgd9681#

你的数据看起来像 c 链接到 b ，不是 a . 假设是这样，您可以使用窗口函数：

select a.*, b.*, c.*
from a join
     b
     on b.a_id = a.id join
     (select c.*,
             row_number() over (partition by c.b_id order by c.created) as seqnum_asc,
             row_number() over (partition by c.b_id order by c.created desc) as seqnum_desc
      from c
     ) c
     on b.id = c.b_id and
        1 in (seqnum_asc, seqnum_desc);

赞(0）回复(0）举报 2021-07-24

pbwdgjma2#

您的查询应该是

select A.id, foo.id, foo.created
from A
join B on (A.id = B.A_id)
join (select *
      from C
            where C.created = (select min(created) from C as C1 where C.B_id=C1.B_id) or
                  C.created = (select max(created) from C as C1 where C.B_id=C1.B_id)
) as foo
on B.id = foo.B_id

赞(0）回复(0）举报 2021-07-24

我来回答

sql-从一对多关系中选择最大和最小行

2条答案

相关问题

热门标签

最新问答