有用的SQL高级函数与查询

一.序号函数

# 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;

 

三.联合查询

Ø 集合运算

操作两组查询结果，进行交集、并集、减集运算

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;

 

五.使用自连接把表中所有重复的字段都查出来

select distinct s.* from student s, student s1 where s.id <> s1.id and s.sex = s1.sex;

具体例子：

create table a(id int,age int)

insert a select 1,20
union all select 2,20
union all select 3,21
union all select 4,22
union all select 5,23
union all select 6,23

select distinct s.* from a s, a s1 where s.id <> s1.id and s.age = s1.age;

 

六.自定义函数：返回table对象

create function fun_find_stuRecord(@id int)

    returns table

as

    return (select * from student where id = @id);

go

select * from dbo.fun_find_stuRecord(2);