sql - 自定义查询以显示具有重复计数的记录及其重复出现

Question

请引用下面的脚本

            declare @table1 table
            (
            col1 int
            )  

            insert into @table1 values(1)
            insert into @table1 values(3)
            insert into @table1 values(3)
            insert into @table1 values(6)
            insert into @table1 values(4)
            insert into @table1 values(4)
            insert into @table1 values(4) 

下面的查询给出了

            select col1 ,COUNT(col1) cntCol1 from @table1 group by col1

这个输出

            ----------------
            col1   | cntCol1
            -------| -------
            | 1    | 1    | 
            | 3    | 2    | 
            | 4    | 3    | 
            | 6    | 1    | 
            ---------------

是否有可能得到下面的输出

            ----------------
            col1   | cntCol1
            -------| -------
            | 1    | 1    | 
            | 3    | 1    | 
            | 3    | 2    |  
            | 4    | 1    |  
            | 4    | 2    |  
            | 4    | 3    | 
            | 6    | 1    | 
            ---------------

如果可以，能否请您帮我查询一下。

谢谢，亦森。

Answer 1

SQL Fiddle

      SELECT col1, ROW_NUMBER() OVER (partition by col1 order by col1) cntCol1
        FROM @table1
    ORDER BY col1, cntCol1    

示例数据:

 declare @table1 table
            (
            col1 int
            )  

            insert into @table1 values(1)
            insert into @table1 values(3)
            insert into @table1 values(3)
            insert into @table1 values(6)
            insert into @table1 values(4)
            insert into @table1 values(4)
            insert into @table1 values(4) 

结果:

| COL1 | CNTCOL1 |
------------------
|    1 |       1 |
|    3 |       1 |
|    3 |       2 |
|    4 |       1 |
|    4 |       2 |
|    4 |       3 |
|    6 |       1 |