常用SQL语法

1.row_number()获取行号

select *
from (select *,num=row_number() 
over (partition by syxh order by lrrq desc) from table1)A
where A.num=1--获取相同首页序号，第一行的数据

2.while循环

declare @i int
declare @cxrq varchar(8)
set @i=10--循环次数
while @i>0
begin
select @cxrq=convert(varchar(8),dateadd(d,-@i,getdate()),112)
exec proc1 @cxrq--循环执行存储
@i=@i-1
end

3.行转列pivot

select * from table1 
as P
pivot
(
sum(score) for --score行转列后 列的值
P.subject in([语文],[数学],[英语])--subject需要行转列的列
)as T

4.行转列sum

select name,
sum(case subject when '语文' then score else 0 end) as '语文',
sum(case subject when '数学' then score else 0 end) as '数学'
from table1
group by subject 

5.charindex查找字符串

select charindex(‘test’，‘this Test is Test’)
--结果：6
select charindex(‘test’，‘this Test is Test’,7)
--结果：14

6.一个表字段update至另一个字段

update table1 
set zd=b.zd
from table1 a,(select * from table1)b
where a.id=b.id

7.cursor游标循环

declare @syxh
declare cursortest cursor for (select syxh from table1)
open cursortest
fetch next from cursortest into @syxh
while @@fetch_status=0
begin
update table1 set zd=zd+1 
from table1 where syxh=@syxh
fetch next from cursortest into @syxh
end
close cursortest 
deallocate cursortest