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

前面篇章快线连接：

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

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

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

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

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

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

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

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

（九）：常用SQL系列之：日期计算、分页、跳行与分级等查询

SQL点滴（56）:如何返回包含最大值和最小值的记录？

也就是说，查找表中所有的“两极”的记录，比如说返回员工中所有最高工资和最低工资的记录。我们来看看——

1）MySQL中的实现参考语句：

SELECT

ename,

salary

FROM

employee

WHERE

salary IN ( ( SELECT min( salary ) FROM employee ), ( SELECT max( salary ) FROM employee ) )

=====================

在Where子句中编写两个子查询，以返回最大值和最小值，作为过滤条件来查询即可，

上面的写法也适用PostgreSQL数据库。

------------------------------------

2）Oracle数据库中实现：

select ename ,salary

from (

select ename,salary,

min(salary) over() min_sal,

max(salary) over() max_sal

from employee

) x where salary in (min_sal,max_sal )

这里用函数 min over和max over,实现表中每行都可有最大和最小值，形成内联视图x，然后返回salary为min_sal或max_sal的行，实现查找的目的——返回只包含最大最小值的记录。

此例句也适用DB2和MS SQL。

SQL点滴（57）:如何给查询结果进行等级划分？

也可以理解为类别划分，比如给员工表中的人员进行职位类型划分或薪资等级划分？

我们看看如何实现——

1）MySQL中：

SELECT

( SELECT count( DISTINCT b.job ) FROM employee b WHERE b.job <= a.job ) AS rnk,

a.job

FROM

employee a

ORDER BY1

这是个子查询来实现的，通用也适用PostgreSQL。

2）Oracle中，可以利用函数DENSE_RANK OVER来实现：

select dense_rank() over(order by job) rnk,job from employee

这个实现更简单。上面的写法也适合DB2和MS SQL。

若表中有薪资字段salary，考虑如何把工资进行等级分类呢？动手试试吧？

SQL点滴（58）:关于数据查询时否定问题之一

比如学校选修改课中，没有选择某门课程的学生有哪些，哪些景点无人问津等。其实这句话的意思有2层，即啥课也没选的学生和没有选某课的学生。假设我们有一个学生表student（学生编号sno和姓名sname以及age）,和选课表take(含学生编号sno和课程编号cno) 。

你可能会这样写SQL:

select * from student where sno in (select sno from take where cno !='cs110')。

如果不是数据巧合，这常会有错误：此结果并没回答“谁没有选CS110课程”之问，但回答了“谁选取了不是CS110课程”之问。正确的结果集因该包括没有选任何课的以及选取了除CS110之外的所有学生。那怎么实现呢？

1）MySQL中的示例写法：

select s.sno,sname,s.age from student s,left join take t on (s.sno=t.sno)

group by s.sno,s.sname,s.age

having max(case when t.cno = 'CS110' then 1 else 0 end) = 0

此写法也适合PostgreSQL。

2）SQL Server中写法(用到了case和Max Over函数)：

select distinct sno,sname,age

from (

select s.sno,s.sname,s.age,

max(case when t.cno='CS110' then 1 else 0 end)

over (partition by s.sno,s.sname,s.age )as takes_CS110

from student s left join take t on (s.sno = t.sno)

) x where takes_CS110 = 0

这个也适合DB2.对于Oracle9及更高版也可以用此方法。

对于Oracle8及更早版，有其他方法，可用分组，参考如下：

select s.sno,s.sname,s.age from student s ,take t

where s.sno = t.sno (+) group by s.sno,s.sname,s.age

having max(case when t.cno = 'CS110' then 1 else 0 end )=0

其实这个问题，几个不同的数据库都用了相同技巧，即在结果集中创建一个“布尔”列，用于表示学生是否选取了CS110，如果选了返回1，否则为0.

作为一个小作业，你看看如何把中间结果也显示出来（包含1和0）？

SQL点滴（59）:数据查询时否定问题之二。

查找选取CS112或选取CS114（但不是二者）的学生。也就是说若D和A同时选择了CS112和CS114，则应该排除二人；若S选择了CS113，但没选CS112或CS114，也应该排除。看看下面的语句：

select * from student

where sno in (select sno from take where cno !='CS112' and cno !='CS114' )

貌似解决所求，但结果是错误的。不符合我们上面要求描述。

由于学生可以选取多门课，因此这里的方法是为每个学生返回一行信息，指明了学生选取了CS112、CS114还是二者都选了。那么怎么实现，这里示例如下：

1）MySQL查询语句参考：

SELECT

s.sno,

s.sname,

s.age

FROM

student s,

take t

WHERE

s.sno = t.sno

GROUP BY

s.sno,

s.sname,

s.age

HAVING

sum( CASE WHEN t.cno IN ( 'CS112', 'CS114' ) THEN 1 ELSE 0 END ) =1

这个语句的下发也适合PostgreSQL。

2）Oracle里的语句参考：

select distinct sno,sname,age

from(

select s.sno,s.sname,s.age,

sum(case when t.cno in ('CS112','CS114') then 1 else 0 end )

over (partition by s.sno,s.sname,s.age) as takes_either_or

from student s, take t

where s.sno =t.sno

) x

where takes_either_or =1 .

重点提示：这个问题第一步是采用从表student到表take的内连接。这样，去掉没有选取任何课程的学生，下一步就是使用case表达式，指出学生是否选取了其中的一门课程。

错误语句和后面参考例句的结果如图所示。

SQL点滴（60）:数据查询时否定问题之三

这个查询场景是这样的，即查找选取了CS112而未选取其他课程的学生。你可能这样写SQL语句，如下：

SELECT s.*

FROM student s, take t

WHERE s.sno = t.sno

AND t.cno = 'CS112'

这里可能C同学是唯一选择CS112而未选其他课程的学生，但结果却返回一堆数据，也包括选取其他课程的学生。这个问题换个说法“找到只选CS112课的同学”，就查“谁选了一门课，且是CS112的？”。虽然问题貌似简单，但逻辑关系要搞清楚，拆分了理解，问题想清楚了，接下来看看怎么实现。

1）MySQL的参考示例：

SELECT s.*

FROM student s, take t1, ( SELECT sno FROM take GROUP BY sno HAVING count( * ) = 1 ) t2

WHERE s.sno = t1.sno AND t1.sno = t2.sno AND t1.cno = 'CS112'

使用聚集函数count，确保查询的学生只有一门课。结果如图所示：

上面的写法也适合PostgreSQL数据库。

2）Oracle中的搞法：

SELECT sno, sname, age FROM

(

SELECT s.sno, s.sname, s.age t.cno,

count( t.cno ) over ( PARTITION BY s.sno, s.sname, s.age ) AS cnt

FROM student s, take t

WHERE s.sno = t.sno

) x WHERE cnt = 1 AND cno = 'CS112'

这里使用了count over窗口函数，以确保学生只选一门课程。

关于这个问题，主要编写一个查询回答“哪些学生只选了一门课程？”以及“哪个学生选了CS112课程”这两个问题，所以使用内联视图t2找到第一个问题，然后把内联视图链接到take表，并保留CS112的同学。这就可以了。

你试试有其他方法来实现吧。

好了，这个基本SQL系列就到这了。点个赞，分享出去吧。^_^