如何从同一个表中获取类似列的计数

Question

我有一个列id和值的表。我想选择记录，其中存在具有相同值但具有较低ID但相同值的其他记录的记录。我需要这些的数量。举例来说，如果我有这样的表

id | value 
---+------ 
1 | 1 
2 | 2 
3 | 1 
4 | 3 
5 | 2 
6 | 1 

我需要的答案

id | value | count 
---+-------+------ 
3 | 1 | 1  // 1 other row with value 1 and a lower id 
5 | 2 | 1  // 1 other row with value 2 and a lower id 
6 | 1 | 2  // 2 other rows with value 1 and a lower id. 

我可以通过做

select id as id1, value as value1 from table where exists 
(select id as id2, value as value2 from table 
where value2 = value1 and id1 < id2); 

获得前两列但是我不知道如何得到点数。我应该使用having还是group by来计数？

Answer 1

您可以使用row_number()此：

select t.* 
from (select t.*, 
      row_number() over (partition by value order by id) - 1 as prev_values 
     from t 
    ) t 
where prev_values > 0;