CSharp基础起步第二十期---SqlServer 基础08（子查询，分页查询

在一个sql语句中嵌入了一个查询语句

分为：相关子查询，独立子查询

运算符：= in exists

练习：分页查询

提示：使用函数row_number

2.子查询实例

-----------1.查询出班级中所有24岁的男生(子查询)--------------------

select * from (select * from student where TSGender=1) as stu where TSAge>24

-----------2.查询出高一三班和高二二班的所有学生(子查询)------------

select * from student where TClassId in

(select tclassid from TblClass where TClassName='高一三班' or TClassName='高二二班')

-----------3.查询出的总人数,男同学多少人,数学平均成绩(子查询)------

select?

总人数=(select count(*) from student),

男同学的人数=(select COUNT(*) from student where TSGender=1),

数学的平均成绩=(select AVG(TSMath) from TblScore)

3.分页查询实例

----------------------1.9条到16条的数据-----------------------------

select top 8 * from student where tsid not in (select top 8 tsid from student )

----------------------2.每页7条数据 ，查第三页的数据----------------

select top 7 * from student where tsid not in (select top (2*7) tsid from student)

----------------------3.给查询结果加编号---------------------------------------

select 编号=ROW_NUMBER()over(order by tsid),* from student

-----------------------4.over按照哪一列进行排序 然后row_number()编号-----------

select tsmath,名次=ROW_NUMBER()over(order by tsmath desc) from TblScore

----------------------5.每页三条 ?查第五页的--------------------------------

select * from

(select 编号=ROW_NUMBER() over(order by tsname desc),* from student)as newStu

where newStu.编号 between (5-1)*3+1 and 3*5

----------------------6.每页9条数据 查询13页的----------------------------

select 编号=ROW_NUMBER ()over(order by id),*from MyOrders

where 编号 between 12*9+1 and 9*13

select * from

(select 编号=ROW_NUMBER() over(order by id),* from MyOrders) as Orders

where Orders.编号 between (13-1)*9+1 and 13*9

----------------------7.名次从小到大，分数一样的名词不一样------------

select tsmath,名次=ROW_NUMBER()over(order by tsmath desc) from TblScore

-----------------------8.rank（） 数据相同的名次一样---------------------

select tsmath,65);">rank()over(order by tsmath desc) from TblScore

欢迎关注趣味CSharp，完整笔记与您分享~~~~~~~~

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!