如何在select子句中使用Post in子句（如SQL Server）在Post子句中进行Postgresql子查询？
我正在尝试在PostgreSQL上编写以下查询：
I am trying to write the following query on 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上。我阅读了一下它的文档，似乎可以将其重写为：
This would certainly work on Microsoft SQL Server but it does not at all on postegresql. I read its documentation a bit and it seems I could rewrite it as:
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中的子查询不能引用相同查询级别的其他关系。所以我被困住了。有人知道我怎么能做到吗？
But that returns the following error on postegresql: "subquery in FROM cannot refer to other relations of same query level". So I'm stuck. Does anyone know how I can achieve that?
谢谢
我不确定我是否完全理解您的意图，但是也许以下内容与您想要的很接近：
I'm not sure I understand your intent perfectly, but perhaps the following would be close to what you want:
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才能加入第二个子查询。也许其他人会提出更好的解决方案。
Unfortunately this adds the requirement of grouping the first subquery by id as well as name and author_id, which I don't think was wanted. I'm not sure how to work around that, though, as you need to have id available to join in the second subquery. Perhaps someone else will come up with a better solution.
分享并享受。