【《SQL进阶教程》】1-4 HAVING子句的力量

四、HAVING子句的力量:

? SQL与其他语言的最大不同点在于SQL是一种基于“面向集合”思想设计的语言。在自联结这一节中就已经介绍了面向集合的概念。我们将表看作抽象度较高的集合,这一节将学习以集合为单位进行操作。

一)、寻找缺失的编号:

seq(连续编号) name(名字)
1 迪克
2
3 莱露
5
6 玛丽
8

? 如上表,我们在遇到自动分配id等情况时常常能遇到这样的情况。编号并不是连续的,我们试着查询这张表中是否存在数据缺失。

1、面向过程语言的思路:

  1. 对“连续编号”列按升序或者降序进行排序。
  2. 循环比较每一行和下一行的编号。

2、面向集合(SQL)的解决思路:

? 将表整体看作一个集合。

SELECT ‘存在缺失的编号‘ AS gap 
  FROM SeqTbl 
HAVING COUNT(*) <>  MAX(seq);

? 在WHERE语句中,是无法使用聚集函数的。我们可以使用HAVING语句,现在的HAVING语句是可以单独使用的,不需要GROUP BY语句,但这样是不能在SELECT子句里引用原来的表里的列。

二)、求众数:

例:毕业生收入表:

-- 使用谓词
SELECT income, COUNT(*) AS cnt 
  FROM Graduates
  GROUP BY income
HAVING COUNT(*) >= ALL (SELECT COUNT(*)
						  FROM Graduates
						GROUP BY income);

-- 使用极值函数
SELECT income, COUNT(*) AS cnt 
  FROM Graduates
  GROUP BY income
HAVING COUNT(*) >= (SELECT MAX(cnt)
					  FROM (SELECT COUNT(*) AS cnt
					    	  FROM Graduates
							GROUP BY income) TMP);

三)、 求中位数(例同上):

--非等值自连接
SELECT AVG(DISTINCT income)
  FROM (SELECT T1.income
  		  FROM Graduates T1, Graduates T2
		GROUP BY T1.income
		-- S1的条件
		HAVING SUM(CASE WHEN T2.income >= T1.income THEN 1 ELSE 0 END)
				  >= COUNT(*) / 2
		-- S2的条件
		  AND SUM(CASE WHEN T2.income <= T1.income THEN 1 ELSE 0 END)
		  		  >= COUNT(*) / 2) TMP;

四)、查询不包含NULL的集合:

COUNT函数有两种使用方法,COUNT(*)和COUNT(列名)两种:

区别:

  1. 性能区别;
  2. COUNT(*)可以用于NULL,而列名会排除掉NULL的行。

例:

student_id dpt sbmt_dtae
100 理学院 2005-10-10
101 理学院 2005-9-22
102 文学院
-- 查询sbmt_date列不含NULL的学院

-- (1)使用COUNT函数
SELECT dpt
  FROM Students
 GROUP BY dpt
HAVING COUNT(*) = COUNT(sbmt_date);

-- (2)使用CASE表达式
SELECT dpt
  FROM Students
 GROUP BY dpt
HAVING COUNT(*) = SUM(CASE WHEN sbmt_date IS NOT NULL
						   THEN 1
						   ELSE 0 END);

【《SQL进阶教程》】1-4 HAVING子句的力量

上一篇:读取mysql数据库


下一篇:mysql 重新整理——索引优化一个简单的案例 [十一]