注：having字句只对聚合函数（count，sum，avg）有效，对列名无效，即
having score > 80,这种表达无效。
1） 招聘岗位在100以上的城市
字段岗位和城市，过滤掉岗位总数大于100的数据，输出城市
法1：
select city,count(positionId) as positiontotal from data.dataanalyst
group by city
having count(positionId) >= 100
order by count(positionId) desc
结果
image.png
法2：
select city,if(count(positionId) >= 100,count(positionId),null) as positiontotal
from data.dataanalyst
group by city
order by count(positionId) desc
结果
image.png
2） 电子商务相关岗位招聘超过50的城市
字段岗位名称和城市，提取关键词为岗位中与电子商务相关的以及总数大于50，输出城市
法1：
SELECT city,count(positionId) as total FROM data.dataanalyst
where industryField like "%电子商务%"
group by city
having total >= 50
order by total desc
结果
image.png
法2：
SELECT city,count(positionId) as total FROM data.dataanalyst
group by city
having count(if(industryField like "%电子商务%",1,null)) >= 50
order by total desc
结果
image.png
注：此处的total是该城市招聘的所有岗位的总数，不是仅仅是电子商务相关岗位的总数，原因在于，上述查询语句未指定where
若要显示电子商务相关岗位的总数，可将上述查询语句修改为
SELECT city,count(if(industryField like "%电子商务%",1,null)) as total FROM data.dataanalyst
group by city
having total >= 50
order by total desc
结果
image.png
3） 不同城市中电商相关岗位占该城市招聘总数的占比
字段岗位名称和城市，可先查询到每个城市的电商相关岗位数量和招聘总数，再进行相应计算。
每个城市的电商相关岗位数量和招聘总数
SELECT city,count(if(industryField like "%电子商务%",1,null)) as etotal,count(positionId) as total FROM data.dataanalyst
group by city
结果
image.png
再进行计算
SELECT city,count(if(industryField like "%电子商务%",1,null)) as etotal,count(positionId) as total,count(if(industryField like "%电子商务%",1,null))/count(positionId) as prob
FROM data.dataanalyst
group by city
order by prob desc
结果
image.png
注：上述查询语句，注意到在select中为两个长语句设置别名etotal和total，但在计算时仍然使用原始语句，原因在于select中设置的别名，无法在select中直接使用【会报错：Error code:1054.Unknown column "total" in "field list"】，可在where、group by和order by等语句中使用，上述语句在order by中使用了别名结构
4） 电子商务相关岗位的招聘总量
SELECT count(if(industryField like "%电子商务%",1,null)) as etotal FROM data.dataanalyst
或者
SELECT count(1) as etotal FROM data.dataanalyst
where industryField like "%电子商务%"
结果
image.png
处理字符串函数
left(在哪个字符串中查找，查找左边起前几个元素)
right(在哪个字符串中查找，查找右边起前几个元素)
locate(查找的元素，在哪个字符串中查找，从哪个位置开始查找)
substr(字符串，从哪里开始，截取长度)
1）查找薪资上限和下限【left，right，locate】
查找薪资下限
SELECT salary,left(salary,locate('k',salary)-1) as bottom
FROM data.dataanalyst
where salary like "%-%"
【此处将一些噪声数据如15k以上去掉】
或者
SELECT salary,substr(salary,1,locate('k',salary)-1) as bottom FROM data.dataanalyst
where salary like "%-%"
结果
image.png
查找薪资上限
SELECT salary,substr(salary,locate('-',salary)+1,length(salary)-locate('-',salary)-1) as top FROM data.dataanalyst
where salary like "%-%
或者
SELECT salary,left(right(salary,length(salary)-locate('-',salary)),length(salary)-locate('-',salary)-1) as top FROM data.dataanalyst
where salary like "%-%"
结果
image.png
子查询
1）求薪资上下限的平均值
select salary,(bottom+top)/2 as meansalary from
(SELECT salary,left(salary,locate('k',salary)-1) as bottom,substr(salary,locate('-',salary)+1,length(salary)-locate('-',salary)-1) as top FROM data.dataanalyst
where salary like "%-%") as t
结果
image.png
注：此处有一个需要注意的点，在子查询中的属性（包括别名）都可以在外部的select语句中使用，此处使用了子查询中的bottom和top别名属性
强迫症看着特别想把meansalary写成11.5k的形式
所以加入concat函数
select salary,concat((bottom+top)/2,'k') as meansalary from
(SELECT salary,left(salary,locate('k',salary)-1) as bottom,substr(salary,locate('-',salary)+1,length(salary)-locate('-',salary)-1) as top FROM data.dataanalyst
where salary like "%-%") as t
结果
image.png
2）将薪资分组
select salary,(bottom+top)/2 as meansalary,
case
when (bottom+top)/2 <= 10 then "0-10"
when (bottom+top)/2 <= 20 then "10-20"
when (bottom+top)/2 <= 30 then "20-30"
when (bottom+top)/2 <= 40 then "30-40"
else "40+"
end
as salarylevel
from
(SELECT salary,left(salary,locate('k',salary)-1) as bottom,substr(salary,locate('-',salary)+1,length(salary)-locate('-',salary)-1) as top FROM data.dataanalyst
where salary like "%-%") as t
结果
image.png
此处使用了case when then else方法
case
when 条件1<=10 then “1-1”
when 条件2<=20 then “2-1”（包含条件1，即相当于条件2在（10,20]）
else “3-1”
end
3） 招聘岗位超过100的城市的明细
首先查询到招聘岗位超过100的城市，
SELECT city FROM data.dataanalyst
group by city
having count(positionId) >= 100
结果
image.png
再查询相应城市的明细
select * from data.dataanalyst
where **city** in
(SELECT **city** FROM data.dataanalyst
group by city
having count(positionId) >= 100)
结果
image.png
注：此处，两个city相对应
如果第二个city处为或者不止city一个字段，会报错【Error code:1241.Operand should contain 1 columns****】
网友评论