SQL SERVER 常用技巧

循环遍历表

declare @count int=0,@R int=0
select row_number()over(order by id) as R,* into #t from Student   
select @count=COUNT(0) from #t     
while(@R<@count)
begin
   set  @R=@R+1
   select * from #t where R=@R
end
drop table #t

删除重复数据

Delete T From
 (Select Row_Number() Over(Partition By[Name] order By[ID]) As RowNumber,* From Student)T
Where T.RowNumber >1

字段拼接

SELECT STUFF((SELECT ',' + rtrim(name)
              FROM Student
			  where name<>''
              FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
        1, 1, '') AS names