c++ 编写函数返回两个值最小值_常用SQL系列之（十）：最大最小值与结果分级及否定问题查询方法

本系列为@牛旦教育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语句，如下：

SELECTs.*

FROM student s,take t

WHERE s.sno = t.sno

AND t.cno = 'CS112'

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

1)MySQL的参考示例：

SELECTs.*

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

WHEREs.sno = t1.sno AND t1.sno = t2.sno AND t1.cno = 'CS112'

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

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

2)Oracle中的搞法：

SELECTsno,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系列就到这了。点个赞，分享出去吧。^_^