gpt4 book ai didi

sql - 在 JOIN 查询中使用限制/偏移量

转载 作者:行者123 更新时间:2023-11-29 12:10:39 24 4
gpt4 key购买 nike

我有4张 table

  1. 一个用户帐户

    user_id | username | password                           
    ---------+----------+----------
  2. 项目表

     project_id |         project_name         | category_id 
    ------------+------------------------------+-------------
  3. 一个 user_projects 表(多对多关系)

    accounts_projects_id | account_id | project_id 
    ----------------------+------------+------------
  4. project_messages 表(一个项目会有很多消息)

    message_id | project_id |message| username 
    ------------+------------+--------+---------

登录时,我运行一个查询,使用以下查询获取用户所属项目的数量以及每个项目的消息

SELECT account.user_id,account.username,
array_agg(json_build_object('message',project_messages.message,'username',project_messages.username)) AS messages,
project.project_name
FROM account
JOIN accounts_projects ON account.user_id = accounts_projects.account_id
JOIN project_messages ON accounts_projects.project_id = project_messages.project_id
JOIN project ON project.project_id = accounts_projects.project_id
WHERE account.username=$1
GROUP BY project.project_name,account.user_id

这给了我下面的输出

userid,username, messages (json array object),project_name`
87;"kannaj";"{"{\"message\" : \"saklep\", \"username\" : \"kannaj\"}"}";"Football with Javascript"
87;"kannaj";"{"{\"message\" : \"work\", \"username\" : \"kannaj\"}","{\"message\" : \"you've been down to long in the midnight sea\", \"username\" : \"kannaj\"}","{\"message\" : \"Yeaaaa\", \"username\" : \"house\"}"}";"Machine Learning with Python"
87;"kannaj";"{"{\"message\" : \"holyy DIVVEERRR\", \"username\" : \"kannaj\"}"}";"Beethoven with react"

从 project_messages 表中检索消息时,有没有一种方法可以使用 LIMIT/OFFSET 函数?

最佳答案

为了让我们的示例更简单,假设我们有两个链接表:

t1(id);
t2(id, t1_id);

查询是

select t1.id, array_agg(t2.id)
from t1 join t2 on (t1.id = t2.t1_id)
group by t1.id;

如您所见,它是大型查询的非常简化的变体。

1)数组

select t1.id, (array_agg(t2.id order by t2.id desc))[3:5]
from t1 join t2 on (t1.id = t2.t1_id)
group by t1.id;

此查询与原始查询一样工作,但仅返回数组中等于 offset 2 limit 3 的第 3、4 和 5 个元素。

2) 子查询和横向

select
t1.id,
array_agg(t.x)
from
t1 join lateral
(select t2.id as x from t2 where t1.id = t2.t1_id order by t2.id desc offset 2 limit 3) t on (true)
group by t1.id;

此处 lateral 关键字允许在我们的子查询 (t1.id) 中使用主 from 子句中提到的其他表中的字段。

关于sql - 在 JOIN 查询中使用限制/偏移量,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/37374687/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com