如何在select子句中使用Post in子句(如SQL Server)在Post子句中进行Postg
技术问答
222 人阅读
|
0 人回复
|
2023-09-14
|
我在尝试postgresql以下查询如下:, J2 w D7 b6 N5 [6 H3 h
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 n1group by name,author_id当然可以Microsoft SQL Server但是在postegresql它根本不可用。我读了它的文档,似乎可以重写为:8 w0 _% ]6 n( K g5 _$ c4 v
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 totalgroup by name,author_id但这在postegresql返回以下错误: FROM中子查询不能引用同级别的其他关系。所以我被卡住了。谁知道我能做到这一点?' p; U' o: s! W# K
谢谢
: Z+ w2 G% b' B' y
% q) S$ ]# m Z8 ~ 解决方案: : l& H7 U1 B" u- u# ]3 U# q$ t
我不确定我是否完全理解你的意图,但以下内容可能与你想要的非常接近:2 e1 d( ~7 _ [
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) n1inner 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加入第二个子查询。也许其他人会提出更好的解决方案。) o( d. k: Q$ s) N, J
分享和享受。 |
|
|
|
|
|