如何在 select 子句中使用 join in from 子句(如 SQL Server)执行 Postgresql 子查询?

2022-01-23 00:00:00 postgresql sql subquery sql-server

我正在尝试在 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 以及 name 和 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.

分享和享受.

相关文章