HAVING 子句
mysql:
不一定非得跟在group by后
select * from t_test where user_id in('30010','91082','10217') having minage>30
hive:
必须跟在group by后
在 SQL 中增加 HAVING 子句原因是,WHERE 关键字无法与聚合函数一起使用。
HAVING 子句可以让我们筛选分组后的各组数据。
以下sql只查一次未优化
SELECT
COUNT(company_name) AS countnum,
company_name,
tel_tru,
tel_full
FROM
(
SELECT
company_name,
tel_full,
case
when length(tel_l)<=12 then RIGHT(tel_l,7)
when length(tel_l)>12 then RIGHT(tel_l,12)
end tel_tru
FROM
(
SELECT
company_name,
REPLACE(tel,' ','') tel_l,
tel tel_full
FROM
t_contact) a
) b
GROUP BY
tel_tru
having countnum >= 10