mysql 日期比较 between的用法的意思_常用SQL系列之（九）：日期计算、分页、跳行与分级等查询...

本系列为@牛旦教育IT课堂在微头条上发布的内容，为便于查阅，特辑录于此，都是常用SQL基本用法。

前两篇连接：

(一)：SQL点滴(查询篇)：数据库基础查询案例实战

(二)：SQL点滴(排序篇)：数据常规排序查询实战示例

(三)：常用SQL系列之:记录叠加、匹配、外连接及笛卡尔等

(四)：常用SQL系列之：Null值、插入方式、默认值及复制等

(五)：常用SQL系列之：多表和禁止插入、批量与特殊更新等

(六)：常用SQL系列之：删除方式、数据库、表及索引元信息查询等

(七)：常用SQL系列之：表约束、最大/小值、非null数、平均值等

(八)：常用SQL系列之：列值累计、占比、平均值以及日期运算等

SQL点滴(51)：如何计算两个日期之间相差的月数或年数？

也就是确定两个日期间的月份数或年份数，计算其间差。比如计算第一个员工和最后一个员工聘用日期相差的月份数，以及这些月折合的年数。首先来看——
1)MySQL的实现示例参考：
SELECT
mnth,mnth / 12
FROM
(
SELECT
(YEAR( max_hd ) - YEAR ( min_hd ))*12 +
MONTH ( max_hd ) - MONTH ( min_hd ) AS mnth
FROM
( SELECT min( hiredate ) AS min_hd, max( hiredate ) AS max_hd FROM employee ) x
) y
注意这个写法，使用了函数Year和Month为给定的日期返回4位数年份和两位数月份。这个写法，也适用DB2。
2)MS SQL中的计算参考：
select datediff(month,min_hd,max_hd),datediff(month,min_hd,max_hd)/12
from( SELECT min( hiredate ) AS min_hd, max( hiredate ) AS max_hd FROM employee )x
若是Oracle中，其写法与此类似，只是所有内部函数不同，如下所示：
select months_between(min_hd,max_hd),months_between(min_hd,max_hd)/12
from( SELECT min( hiredate ) AS min_hd, max( hiredate ) AS max_hd FROM employee )x
这种计算，主要要想清楚他们的关系机理，你可以分别显示年份和月份以作对比，比如：
SELECT
YEAR( max_hd ), YEAR( min_hd ),
MONTH ( max_hd ),MONTH ( min_hd )
FROM
( SELECT min( hiredate ) AS min_hd, max( hiredate ) AS max_hd FROM employee ) x

好了，动手试试吧。

SQL点滴(52):如何实现对查询结果集的分页操作?

进一步讲，就是给查询结果分页，或者“滚动显示”所查询的结果。比如针对人员信息表有500条记录，希望每次显示10行，那么酱油50页可以“翻阅”，从第1页到第50页，在操作页面上，体现为1-50页，可以一页页“点击翻页”，顺序查看每一页，如何在SQL中实现呢？首先，我们来看：
1)在MySQL中的实现：
select id,col01，col02，col03，coln from sometable order by id limit 10 offset 0
上面的意思为我们以id为主键排序 ，第一次从第一条记录开始，一次查看前10条记录。需要注意的是，关键字limit 后的数值，指定要查看多少记录，offerset定义了返回结果的第一条记录的偏移位置(就是从排序集中哪条记录开始依次返回10条记录，或跳过哪些记录)，如果体现在页面上，那么offer 0可以看做从头显示第一页10条记录，那么第二页的偏移量offset就是从(2-1)*10，以此类推。当然，具体返回那些字段，根据需要指定。
这种SQL的写法，也适合PostgreSQL。
2)在Oracle中的实现：
select col1,col2,coln
from(
select row_number() over (order by id) as rn,col1,col2,coln from sometable
) x where rn between 1 and 10 .
在where子句的between 和and后的数字，就是表示要返回从那条记录到哪条记录的返回值返回。
此写法同样适用DB2和MS SQL Server。row_number()给每行记录非配唯一序号，以便可以明确返回需要的页记录。
另外，对于Oracle，还有另外一种 方式，使用rownum来生成唯一记录号。
Oracle的ROWNUM的具体SQL实现，自己动手实现一下吧。

SQL点滴(53):小花招—怎样使用SQL语句来跳过表中的指定n行返回所有结果？

比如说，从employee表中每隔一行返回一名员工，或者查找第1、3、5等以此类推的记录，如何实现？其实这个实现可以基于我前面介绍的翻页模式来实现。具体来参考如下实现：
1)MySQL间隔模式返回结果：
SELECT
x.ename
FROM
(
SELECT
a.ename,
( SELECT count( * ) FROM employee b WHERE b.ename <= a.ename ) AS rn
FROM
employee a
) x
WHERE
MOD ( x.rn, 2 ) =1
由于MySQL没有为行分等级或给行编号的函数，所以需要用子查询给行分顺序等级(作为行编号，本查询中用名字来进行分级实现记录的行编号)，然后使用求模操作来跳过行。
上面的SQL语句也适用PostgreSQL。
2)在Oracle中的实现：
select ename from (
select row_number() over (order by ename) rn,ename from employee
) x
where mod(rn,2) = 1
关于求模，上面的写法也适用DB2，若在MS SQL中，则用%运算符。上面的语句将跳过偶数行号。
自己动手试试吧。

SQL点滴(54):如何查询指定部门的员工和部门名称信息，而其余部门只显示部门名称而无员工信息？

比如说，只返回部门编号为2和3的部门员工信息以及部门信息，而编号为1和4的只返回部门信息，如何实现。其实，这个用外连接并在连接中用or逻辑即可。参考SQL如下：

SELECTe.ename,d.dpid,d.dpname,d.dpaddressFROMdepartment dLEFT JOIN employee e ON ( d.dpid = e.departid AND ( e.departid = 2 OR e.departid = 3 ) )ORDER BY 1

注意，order by 1,是指按ename进行默认升序排序，如果是2，则指定位dpid升序排序。
这个写法适合MySQL、DB2、PostgreSQL以及SQL Server。也适用Oracle9i及以上版本。
另外，上面的语句还可以用e.departid进行筛选，然后进行外部链接，如下所示：
SELECT
e.ename,
d.dpid,
d.dpname,
d.dpaddress
FROM
department d
LEFT JOIN
( select ename,departid from employee where departid in(2,3)
) e on (e.departid = d.dpid )
order by 1
自己动手试试吧。如果有错，记得找找原因哦 ^_^

SQL点滴(55):如何通过查询语句为返回结果分级并返回感兴趣的结果？

比如说，对employee表的工资进行分级，然后返回想要的等级的相关数据。比如薪资一样的为同级别，不同的为不同级别。如果有10条记录，那分出来级别是小于等于10的。
在MySQL中，我使用了子查询，为每个工资创建了一个等级，然后利用等级做条件返回结果，参考语句如下：
第一步，可以查分级结果；
SELECT
( SELECT count( DISTINCT b.salary ) FROM employee b WHERE a.salary <= b.salary ) AS rnk,
a.salary,
a.ename
FROM
employee a order by 1
第二步，把分级结果作为查询对象，添加where子句来返回感兴趣的结果：
select * from x where rnk<=3
x指代第一步的查询语句(作为第二步的子查询)，试试吧。我这里演示的两步结果如图所示。

另外，可以看看本号@牛旦教育IT课堂 中关于JPA学习文章：系列总结：JPA核心接口综合实战案例