如何像SQL Server一样在select子句中执行PostgreSQL子查询，并在from子句中加入？
如何像SQL Server一样在select子句中执行PostgreSQL子查询，并在from子句中加入？
提问于 2010-06-09 18:17:48
我尝试在postgresql上编写以下查询：
select name, author_id, count(1),
(select count(1)
from names as n2
where n2.id = n1.id
and t2.author_id = t1.author_id
)
from names as n1
group by name, author_id这当然可以在Microsoft SQL Server上工作，但在postegresql上完全不起作用。我看了一下它的文档，似乎可以把它重写成：
select name, author_id, count(1), total
from names as n1, (select count(1) as total
from names as n2
where n2.id = n1.id
and n2.author_id = t1.author_id
) as total
group by name, author_id但这会在postegresql上返回以下错误：“FROM中的子查询不能引用相同查询级别的其他关系”。所以我被卡住了。有人知道我如何做到这一点吗？
谢谢
回答 4
Stack Overflow用户
回答已采纳
发布于 2010-06-09 19:25:29
我不确定我是否完全理解你的意图，但也许下面的内容会接近你想要的：
select n1.name, n1.author_id, count_1, total_count
from (select id, name, author_id, count(1) as count_1
from names
group by id, name, author_id) n1
inner join (select id, author_id, count(1) as total_count
from names
group by id, author_id) n2
on (n2.id = n1.id and n2.author_id = n1.author_id)不幸的是，这增加了按id、名称和author_id对第一个子查询进行分组的要求，我认为这是不必要的。不过，我不确定如何解决这个问题，因为您需要有可用的id来连接第二个子查询。也许其他人会想出更好的解决方案。
分享和享受。
Stack Overflow用户
发布于 2018-03-19 17:51:54
我知道这很老，但是因为Postgresql 9.3可以选择使用关键字“横向”来在连接中使用相关子查询，所以问题中的查询将如下所示：
SELECT
name, author_id, count(*), t.total
FROM
names as n1
INNER JOIN LATERAL (
SELECT
count(*) as total
FROM
names as n2
WHERE
n2.id = n1.id
AND n2.author_id = n1.author_id
) as t ON 1=1
GROUP BY
n1.name, n1.author_idStack Overflow用户
发布于 2010-06-09 20:07:51
我只是在上面的评论中根据Bob Jarvis的回答，用我所需要的最终sql的格式化版本来回答：
select n1.name, n1.author_id, cast(count_1 as numeric)/total_count
from (select id, name, author_id, count(1) as count_1
from names
group by id, name, author_id) n1
inner join (select author_id, count(1) as total_count
from names
group by author_id) n2
on (n2.author_id = n1.author_id)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/3004887
复制相关文章
相似问题