#having 子查询求众数
SELECT
g.income,
count(*)
FROM
Graduates g
GROUP BY
g.income
HAVING
count(*) >= ALL (
SELECT
count(*)
FROM
Graduates g1
GROUP BY
g1.income
)
#having 子句进行自连接 求中位数
#要根据大小关系生成子集时,就用到非等值自连接
SELECT
avg(tmp.income)
FROM
(
SELECT
g1.income AS income
FROM
Graduates g1,
Graduates g2
GROUP BY
g1.income
HAVING
sum(
CASE
WHEN g1.income >= g2.income THEN
1
ELSE
0
END
) >= count(*) / 2
AND sum(
CASE
WHEN g1.income <= g2.income THEN
1
ELSE
0
END
) >= count(*) / 2
) tmp
#用关系除法进行购物篮分析
#where 是调查集合元素性质的,having 是调查集合性质的
#1:关系排除
SELECT
s.shop
FROM
ShopItems s,
Items t
WHERE
s.item = t.item
GROUP BY
s.shop
HAVING
count(*) = (SELECT count(*) FROM Items)
#2,精确关系排除
SELECT
s.shop
FROM
ShopItems s left join Items t
on s.item = t.item
GROUP BY
s.shop
HAVING
count(s.item) = (SELECT count(*) FROM Items)
and
count(t.item) = (SELECT count(*) FROM Items)