postgresql - 在 PostgreSQL 中抑制重复的列值？

Question

例如，如果我们有以下值:

username             Category
-------------------- ----------
Brian                cs
Tom                  cs
Joe                  cs
Allen                cs
Bill                 ts
Steven               ts
Fred                 ts
Ted                  ts

我需要一个输出作为

username             Category
-------------------- ----------
Brian                cs
Tom                  
Joe                  
Allen                
Bill                 ts
Steven               
Fred                 
Ted      

我试过

1) T-SQL How to select rows without duplicate values from one column?

2) http://www.rajapet.com/2010/04/suppressing-repeated-column-value-in.html

3) How to select records without duplicate on just one field in SQL?

没有什么效果很好(我所期望的)

还有其他办法吗？

Answer 1

您可以使用 window function为此:

select username, 
       case when rn = 1 then cat else '' end as category
from (
   select username, 
          category as cat, 
          row_number() over (partition by category order by username) as rn
   from the_table
) t
order by cat, username;

SQLFiddle:http://sqlfiddle.com/#!15/423ed/4