我有一个查询结果,有3个不同的p_id(3160、3301、41)
我想选择count(不同的p_id),如图所示p_id_count_true(见图片),p_id_count不正确,
如何编写子查询并获取p_id_count_true

最佳答案

你似乎想要:

select . . .,
       count(distinct pid) over () as num_pids
from t

唉,Postgres不支持distinct窗口函数。一种解决方法是:
select . . .,
       sum( (seqnum_pid = 1)::int ) over () as num_pids
from (select t.*, row_number() over (partition by pid order by pid) as seqnum_pid
      from t
     ) t

有些人更喜欢dense_rank()方法:
select . . .,
       max( seqnum_pid ) over () as num_pids
from (select t.*, dense_rank() over (order by pid) as seqnum_pid
      from t
     ) t

关于sql - 在表新字段中选择计数不同的列,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/51107744/

10-11 23:54