SQL进阶：HAVING子句

1、查询缺失的编号 

-- 查询是否存在缺失的编号
select '存在缺失编号' from bs_visit HAVING count(*) <> MAX(id);


-- 查询最小的缺失编号
SELECT
	min(id + 1)
FROM
	bs_visit
WHERE
	id + 1 NOT IN (SELECT id FROM bs_visit)
 

2、查询众数（数量最多的值）：

SELECT
	dm_desc2,
	count(*)
FROM
	bs_visit
GROUP BY
	dm_desc2
HAVING
	count(*) >= (
		SELECT
			MAX(c)
		FROM
			(
				SELECT
					count(*) AS c
				FROM
					bs_visit
				GROUP BY
					dm_desc2
			) tmp
	)
-- 每个派生表都必须有自己的别名（此处用了tmp）

3、求中位数

-- 思路：自连接，以A表进行分组，1、过滤出A表值比B表一半的数量的值都大的A表值。
-- 2、过滤出A表值比B表一半的数量的值都小的A表值。
-- 3、取交集AND， 然后进行求平均值
SELECT
	AVG(DISTINCT id)
FROM
	(
		SELECT
			A.id
		FROM
			bs_visit A,
			bs_visit B
		GROUP BY
			A.id
		HAVING
			SUM(
				CASE
				WHEN A.id >= B.id THEN
					1
				ELSE
					0
				END
			) >= COUNT(*)/2
		AND SUM(
			CASE
			WHEN A.id <= B.id THEN
				1
			ELSE
				0
			END
		) >= COUNT(*)/2
	) tmp

-- 举例： 求 1 2 3 4 的中位数，  答案应该是5 。
-- 自连接 A 表 1234 B表 1234
-- 1、过滤出A表值比B表一半的数量的值都大的A表值。   2 3 4 （含等号，故有2）
-- 2、过滤出A表值比B表一半的数量的值都小的A表值。   1 2 3 （含等号，故有3）
-- 3、取交集AND， 然后进行求平均值   （2 2 3 3 ）这是交集，  平均值是5 ，故中位数为5

4、查询不包含NULL的集合

SELECT
	dm_desc2
FROM
	bs_visit
GROUP BY
	dm_desc2
HAVING
	count(*) = count(`status`);

-- 等效
SELECT
	dm_desc2
FROM
	bs_visit
GROUP BY
	dm_desc2
HAVING
	count(*) = SUM(
		CASE
		WHEN `status` IS NOT NULL THEN
			1
		ELSE
			0
		END
	)

-- count(*) 用于统计行数
-- count(column) 会排除NULL的列，仅统计非NULL的数量。
-- count(主键) 效率最高