常用的sql语句的总结（分页&纵转横）

————————————————分页—————————————————

select * from Department

select top 4 * from Department
--=============分页查询
@pageindex--表示当前第几页1	2
@pageSize--每页显示多少条数据4
--
select * from departwithrowindex
where rowindex between @pageSize*(@pageIndex-1)+1 and @pageSize*@pageIndex
num1=@pageSize*(@pageIndex-1)+1
num2=@pageSize*@pageIndex

create view departWithRowIndex
as
select *,ROW_NUMBER() over(order by did desc) as rowindex from Department

select * from departwithrowindex
where rowindex between 9 and 12

————————————————纵转横—————————————————

select * from users

create table t1(
	tid int primary key identity(1,1),
	tname nvarchar(10),
	tCourse nvarchar(10),
	tGrade int
)

select * from t1

insert into t1
select 'sk','English',60
union
select 'sk','Math',70
union
select 'sk','Chinese',80
union
select 'jk','Chinese',90
union
select 'jk','Math',100
select * from t1

select tname,sum(case tcourse when 'chinese' then tGrade else 0 end )as chinse,
sum(case tcourse when 'math' then tGrade else 0 end )as math,
sum(case tcourse when 'english' then tGrade else 0 end )as english
from t1
group by tname