1.RowNumber用法:
Row_Number() over (order by col desc)
Row_Number() over (partiton by colA,colB order by colA) //按colA,colB分组后按colA排序
2.while嵌套循环
declare @CourseNum int
declare @ChapterNum int
set @ChapterNum=1
set @CourseNum=1
while(@courseNum<=10)
begin
declare @courseName varchar(30)
set @CourseName='Course'+Convert(Varchar(30),@courseNum)
insert into course values(@CourseName)
while(@ChapterNum<=20)
begin
declare @ChapterName varchar(40)
set @ChapterName=@courseName+'_Chapter'+Convert(Varchar(30),@ChapterNum)
insert into chapter values(@ChapterName,@CourseNum)
set @ChapterNum=@ChapterNum+1
continue
end
set @ChapterNum=1--一定要再重置它的值 否则 它一直保持为 20
set @courseNum=@courseNum+1
continue
end
select * from chapter
3.游标循环
DECLARE
@id INT, @value VARCHAR(10);
BEGIN
-- 定义游标.
DECLARE c_test_main CURSOR FAST_FORWARD FOR
SELECT id, value FROM test_main;
-- 打开游标.
OPEN c_test_main;
--填充数据.
FETCH NEXT FROM c_test_main INTO @id, @value;
--假如检索到了数据,才处理.
WHILE @@fetch_status = 0
BEGIN
PRINT @value;
--填充下一条数据.
FETCH NEXT FROM c_test_main INTO @id, @value;
END;
-- 关闭游标
CLOSE c_test_main;
--释放游标.
DEALLOCATE c_test_main;
END;
4.行列转换
select Student as '姓名',
max(case Subject when '语文' then Score else 0 end) as '语文' ,--如果这个行是“语文”,就选此行作为列
max(case Subject when '英语' then Score else 0 end ) as '英语'
from Scores
group by Student
order by Student
另外一种方法,用pivot
/*
pivot(
聚合函数(要转成列值的列名)
for 要转换的列
in(目标列名)
)
*/
select Student as '姓名',
avg(语文) as '语文',
avg(英语) as '英语'
from Scores
pivot(
avg(Score) for Subject
in (语文,英语)
)as NewScores
group by Student
order by Student asc
行列转换转自:http://www.cnblogs.com/oneivan/archive/2012/02/16/2353796.html