第四章:数据的检索(Select)
4.4、限制结果集行数(Top+窗口函数row_number())
只检索部分行,前三行,第三行到第七行
Top放在select后,语法:Select Top 行数
select top 3* from T_Employee where FNumber not in (select top 5 FNumber from T_Employee order by FSalary DESC) order by FSalary DESC
row_number计算每行数据在结果集中的行数(从1开始),语法row_number over (排序规则)
查看数据中每条记录的行号select row_number() over(order by FSalary),FNumber,FName,FSalary,FAge from T_Employee
row_number 不能用在where语句中。返回第三行到第五行
select * from (
select row_number() over (order by FSalary DESC) as rownum,FNumber from T_Employee
) as a where a.row.num>=3 and a.rownum<=5
4.5、抑制数据重复(distinct)
剔除重复的数据,只显示一条
select distinct name from Student
4.6、计算字段
干嘛的呢,说不出来,先看完再说吧
4.6.1、常量字段在查询结果中增加本来不存在但是知道并需要显示的列,公司名,国籍select 'China' as Country,0310 as Phone,FName from T_Employee4.6.2、字段间计算工资指数:工龄与工资乘积select FName,FAge * FSalary as FSalaryIndexfrom T_Employee4.6.3、数据处理函数(Len+Substring+sin正弦+abs绝对值)Len记录的长度,Substring取得字符串的子串的函数select FName,Len(FName) as namelength from T_Employee where FName is not nullselect Fname,sunstring(Fname,2,3) from T_Employee where Fname is not null4.6.4、字符串拼接(+)select '员工号为'+FNumber+'的员工姓名'+Fname from T_Employee where FName is not null4.6.5、字段其他用途(between and+max ,min+set年龄加一)select * from T_Eployee where FSalary between 100 and 200select max(Fsalary) ,min (FSalary) from t_employeeupdate t_Employee set FAge=FAge+1
4.7、不从实体表中取数据
不带from
select 1 as Number
select Len(‘asd’)
4.8、联合结果集(Union)
两个或多个查询结果集联合为一个结果集(合并只是将本来是其他表或不同列的数据列在一列里面)
4.8.1、simple union(将两表的两列合并成一列)
select FNumber,Fname from T_Employee
union
select FidCardNumber,Fname from T_TempEmployee
4.8.2、Union的原则
必须每个结果集有相同的列数,每个结果集的列必须类型相容
4.8.3、union all
在默认的合并中,会将两列相同的记录保留一条,所以必须用union all来保留所有的记录
select FName ,FAge from t_employee
union all
select Fname,FAge from T_TempEmployee
4.8.4、应用