– 根据排序子句给出递增的序号，但是存在并列并且跳空

– 顺序递增

select id, name, rank() over(order by cid) as rank from student;

– 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可以与以上三个函数联合使用。

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;

? 集合运算

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

– union 并集、不重复

select id, name from student where name like 'ja%'

union select id, name from student where id = 4;

– union all 并集、重复

select * from student where name like 'ja%'

union all select * from student;

– 交集相同

select * from student where name like 'ja%'

intersect select * from student;

– 减集（除相同部分）

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 s.id, s.name, c.id, c.name from student s, classes c where s.cid = c.id;



-- 左连接 (左为基表)

select s.id, s.name, c.id, c.name from student s left join classes c on s.cid = c.id;



-- 右连接 (右为基表)

select s.id, s.name, c.id, c.name from student s right join classes c on s.cid = c.id;



-- 内连接 (等值联合)

select s.id, s.name, c.id, c.name from student s inner join classes c on s.cid = c.id;



-- 交叉联接查询，结果是一个笛卡儿乘积

select s.id, s.name, c.id, c.name from student s ,classes c



-- 自连接

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

函数

– 聚合函数 (max最大值、min最小值、count统计、avg平均值、sum求和、var求方差)

select

max(age) max_age, 

min(age) min_age, 

count(age) count_age, 

avg(age) avg_age, 

sum(age) sum_age, 

var(age) var_age 

from student;

– 日期时间函数

select dateAdd(day, 3, getDate());

select dateAdd(year, 3, getDate());

select dateAdd(hour, 3, getDate());

select dateDiff(day, ‘2011-06-20’, getDate());

select dateDiff(second, ‘2011-06-22 11:00:00’, getDate());

select dateDiff(hour, ‘2011-06-22 10:00:00’, getDate());

select dateName(month, getDate());

select dateName(minute, getDate());

select dateName(weekday, getDate());

select datePart(month, getDate());

select datePart(weekday, getDate());

select datePart(second, getDate());

select day(getDate());

select day(‘2011-06-30’);

select month(getDate());

select month(‘2011-11-10’);

select year(getDate());

select year(‘2010-11-10’);

select getDate();

select getUTCDate();

– 数学函数

select pi();