SQL 选择查询

Question

我有以下 SQL 表:

CarId | CustomerId | Price  
 1    |     1      | 5000  
 2    |     3      | 6000  
 3    |     4      | 6000  
 4    |     1      | 6000  
 5    |     3      | 6000  
 6    |     6      | 6000  

我想创建一个新列:

CarId | CustomerId | Price | CarPerCustomer
 1    |     1      | 5000  |       1/2
 2    |     3      | 6000  |       1/3
 3    |     4      | 6000  |       1/1
 4    |     1      | 6000  |       2/2
 5    |     3      | 6000  |       2/3
 6    |     3      | 6000  |       3/3

所以基本上 CarPerCustomer = 行数/(该客户拥有的汽车总数)。有人吗？

Answer 1

使用窗口函数

select CarId, CustomerId, Price
    , cast(row_number() over (partition by CustomerId order by CustomerId) as varchar)
        + '/' + cast(count(*) over (partition by CustomerId ) as varchar)
    from t