如何在两个与PostgreSQL中某些ID相关的数据之间选择数据

发布于 2025-01-23 09:20:50 字数 547 浏览 0 评论 0原文

所以我有2张桌子。在第一表中，我需要将ID与Min和Max DateTime联系起来，

SELECT id, min (datetime) as earlytime, max(datetime) as latesttime
FROM table1
group by id

结果我可以使用ID和2个时间戳约5K。

我需要使用此信息从Table2中获得具有ID的行，其中每个ID的日期时间分别在它们的早期和最新时间之间。或者只是带有ID和时间的行，比最新时间少。

仍然不知道该怎么做。

提出了类似的内容

SELECT ID, source, amount, type, datetime
FROM table2
WHERE EXISTS (SELECT ID, min (datetime) as earlytime, max (datetime) as latetime
FROM table1
group by ID)

，但我想它只是显示了匹配Table1 ID的任何行，

有人可以帮助我吗？

原文

So I have 2 tables. In first table I need to link ID with min and max datetime, which I did with

SELECT id, min (datetime) as earlytime, max(datetime) as latesttime
FROM table1
group by id

as a result I get around 5k with ID and 2 timestamps.

I need to use this info to get rows with ID from table2 where datetime for each ID respectively is between their earlytime and latesttime. Or just rows with ID and time which less than latesttime.

Still can't figure out how to do that.

Came up with something like that

SELECT ID, source, amount, type, datetime
FROM table2
WHERE EXISTS (SELECT ID, min (datetime) as earlytime, max (datetime) as latetime
FROM table1
group by ID)

But I guess it just shows any rows that match ID from table1

Anyone can help me with that?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

_蜘蛛 2025-01-30 09:20:50

也许您可以加入并获得以下结果

select A.* 
from table2 A 
join 
(
SELECT id, min (datetime) as earlytime, max(datetime) as latesttime
FROM table1
group by id
)B
on A.id=B.id and 
B.timecol between earlytime and latesttime

maybe you can join and get the results like below

select A.* 
from table2 A 
join 
(
SELECT id, min (datetime) as earlytime, max(datetime) as latesttime
FROM table1
group by id
)B
on A.id=B.id and 
B.timecol between earlytime and latesttime

回复收藏 0 原文

眼趣 2025-01-30 09:20:50

您可以使用CTE或子问题的组合（无论您喜欢哪个）和（）之间的来实现您的预期输出

with cte as (
   select
      id
      ,min(datetime) as earlytime
      ,max(datetime) as latesttime
   from table1
   group by id
)

select
   c.id
   ,c.earlytime
   ,c.latesttime
   ,t2.* /*Table2 columns*/
from cte as c
inner join table2 as t2 ON c.id = t2.id 
  and t2.datetime between c.earlytime and c.latesttime

You can use a combination of a CTE or sub-query (whichever you prefer) and BETWEEN()to achieve your expected output

with cte as (
   select
      id
      ,min(datetime) as earlytime
      ,max(datetime) as latesttime
   from table1
   group by id
)

select
   c.id
   ,c.earlytime
   ,c.latesttime
   ,t2.* /*Table2 columns*/
from cte as c
inner join table2 as t2 ON c.id = t2.id 
  and t2.datetime between c.earlytime and c.latesttime

回复收藏 0 原文

江城子 2025-01-30 09:20:50

SELECT T2.ID,T2.SOURCE,T2.AMOUNT,T2.TYPE,T2.DATETIME
FROM TABLE2 AS T2
JOIN
(
  SELECT id, min (datetime) as earlytime, max(datetime) as latesttime
  FROM table1
  group by id
)X ON T2.ID=X.ID AND T2.DATETIME BETWEEN X.earlytime AND X.latesttime

SELECT T2.ID,T2.SOURCE,T2.AMOUNT,T2.TYPE,T2.DATETIME
FROM TABLE2 AS T2
JOIN
(
  SELECT id, min (datetime) as earlytime, max(datetime) as latesttime
  FROM table1
  group by id
)X ON T2.ID=X.ID AND T2.DATETIME BETWEEN X.earlytime AND X.latesttime

回复收藏 0 原文

~没有更多了~