SQL Server T-SQL高级查询（三） |
||
|---|---|---|
| 作者：admin 更新日期：2012年06月28日 点击： | ||
| Ø 排序函数 排序在很多地方需要用到，需要对查询结果进行排序并且给出序号。比如： 1、 对某张表进行排序，序号需要递增不重复的 2、 对学生的成绩进行排序，得出名次，名次可以并列，但名次的序号是连续递增的 3、 在某些排序的情况下，需要跳空序号，虽然是并列 基本语法 排序函数 over([分组语句] 排序子句[desc][asc]) 排序子句 order by 列名, 列名
分组子句 partition by 分组列, 分组列
# row_number函数 根据排序子句给出递增连续序号 按照名称排序的顺序递增 select s.id, s.name, cid, c.name, row_number() over(order by c.name) as number from student s, classes c where cid = c.id;
# rank函数函数 根据排序子句给出递增的序号，但是存在并列并且跳空 顺序递增 select id, name, rank() over(order by cid) as rank from student; 跳过相同递增 select s.id, s.name, cid, c.name, rank() over(order by c.name) as rank from student s, classes c where cid = c.id;
# dense_rank函数 根据排序子句给出递增的序号，但是存在并列不跳空 不跳过，直接递增 select s.id, s.name, cid, c.name, dense_rank() over(order by c.name) as dense from student s, classes c where cid = c.id;
# partition by分组子句 可以完成对分组的数据进行增加排序，partition by可以与以上三个函数联合使用。 利用partition by按照班级名称分组，学生id排序 select s.id, s.name, cid, c.name, row_number() over(partition by c.name order by s.id) as rank from student s, classes c where cid = c.id; select s.id, s.name, cid, c.name, rank() over(partition by c.name order by s.id) as rank from student s, classes c where cid = c.id; select s.id, s.name, cid, c.name, dense_rank() over(partition by c.name order by s.id) as rank from student s, classes c where cid = c.id;
# ntile平均排序函数 将要排序的数据进行平分，然后按照等分排序。ntile中的参数代表分成多少等分。
select s.id, s.name, cid, c.name,
ntile(5) over(order by c.name) as ntile from student s, classes c where cid = c.id;
Ø 集合运算 操作两组查询结果，进行交集、并集、减集运算 1、 union和union all进行并集运算
--union 并集、不重复
select id, name from student where name like 'ja%'
union
select id, name from student where id = 4; --并集、重复 select * from student where name like 'ja%' union all select * from student;
2、 intersect进行交集运算 --交集（相同部分） select * from student where name like 'ja%'
intersect
select * from student;
3、 except进行减集运算 --减集（除相同部分） select * from student where name like 'ja%'
except
select * from student where name like 'jas%';
Ø 公式表表达式 查询表的时候，有时候中间表需要重复使用，这些子查询被重复查询调用，不但效率低，而且可读性低，不利于理解。那么公式表表达式可以解决这个问题。 我们可以将公式表表达式（CET）视为临时结果集，在select、insert、update、delete或是create view语句的执行范围内进行定义。 --表达式 with statNum(id, num) as (
select cid, count(*)
from student
where id > 0
group by cid
) select id, num from statNum order by id; with statNum(id, num) as (
select cid, count(*)
from student
where id > 0
group by cid
) select max(id), avg(num) from statNum;
Ø 连接查询 1、 简化连接查询 --简化联接查询 select s.id, s.name, c.id, c.name from student s, classes c where s.cid = c.id;
2、 left join左连接 --左连接 select s.id, s.name, c.id, c.name from student s left join classes c on s.cid = c.id;
3、 right join右连接 --右连接 select s.id, s.name, c.id, c.name from student s right join classes c on s.cid = c.id;
4、 inner join内连接 --内连接 select s.id, s.name, c.id, c.name from student s inner join classes c on s.cid = c.id; --inner可以省略 select s.id, s.name, c.id, c.name from student s join classes c on s.cid = c.id;
5、 cross join交叉连接 --交叉联接查询，结果是一个笛卡儿乘积 select s.id, s.name, c.id, c.name from student s cross join classes c
--where s.cid = c.id;
6、 自连接（同一张表进行连接查询） --自连接 select distinct s.* from student s, student s1 where s.id <> s1.id and s.sex = s1.sex;
|
||
| SqlServer,高级查询,T-Sql,排序函数,集合运算,YaDong网,徐亚东,亚东网 | ||
| 上一篇：SQL Server T-SQL高级查询（二）——嵌套查询 | ||
| 下一篇：SQL Server T-SQL高级查询（四）——函数 |
您当前位置：YaDong网 >> 一些日志 >> SqlServer相关 >> 浏览文章
