如何在select子句中使用Post in子句(如SQL Server)在Post子句中进行Postg
技术问答
223 人阅读
|
0 人回复
|
2023-09-14
|
我在尝试postgresql以下查询如下:
0 X% Y: K; _! F) s8 d aselect 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它根本不可用。我读了它的文档,似乎可以重写为:5 C. }, R* v/ y% \7 K
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中子查询不能引用同级别的其他关系。所以我被卡住了。谁知道我能做到这一点?; X V5 b4 ~6 `- U4 y$ w2 ?
谢谢& ^% ?4 m- Q: _: [- E
# N$ N: U# o, d) l& e3 ]" S b
解决方案: / h& J+ H: w' H
我不确定我是否完全理解你的意图,但以下内容可能与你想要的非常接近:
2 Y, }4 g" Y3 }) z; sselect 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加入第二个子查询。也许其他人会提出更好的解决方案。
) P7 u4 v9 `2 |$ w& O; N分享和享受。 |
|
|
|
|
|