SQL集锦

1. 生成带序号的集合

    select ROW_NUMBER() over(order by id asc) as 'rowNumber', * from table1

2.查询集合的 第 1  到第 5条数据

    select * from 
    (select ROW_NUMBER() over(order by id asc) as 'rowNumber', * from table1) as temp
    where rowNumber between 1 and 5

3.取每个学科的前3名数据 
    select * from 
    ( 
        select subject,name,score,ROW_NUMBER() over(PARTITION by subject order by score desc) as num from #score 
    ) T where T.num <= 3 order by subject 

4.多行合并到一行

   select stuff((select ','+itemid from useritem for xml path('')),1,1,'')