求写一个SQL话语，分组查询_Sql Server

求写一个SQL语句，分组查询！
表TB如下:

type val
-------------------------
A 3
A 7
B 2
C 12
A 23
A 33
C 29
C 10
B 13
B 56
C 21
…… ……
通过分组查询，得到分组里的倒序排序里的前三个数字。
得到的表:

type val
------------------------
A 33
A 23
A 7
B 56
B 13
B 2
C 29
C 21
C 10

另：type可能很多，还有D，E，F等

------解决方案--------------------

SQL code

create table tb(type char(1), val int)insert into tbselect 'A', 3 union allselect 'A', 7 union allselect 'B', 2 union allselect 'C', 12 union allselect 'A', 23 union allselect 'A', 33 union allselect 'C', 29 union allselect 'C', 10 union allselect 'B', 13 union allselect 'B', 56 union allselect 'C', 21with t as(select row_number() over(partition by [type] order by val desc) rn, [type],[val] from tb)select [type],[val]from twhere rn<=3/*type  val---- -----------A     33A     23A     7B     56B     13B     2C     29C     21C     12(9 row(s) affected)*/
------解决方案--------------------
SQL codeselect type ,valfrom (select  type ,val,row_number() over(partition by type order by val desc) as rnfrom TB) awhere a.rn<=3