统计薪酬最高的和薪酬分布情况,下面包括表的创建和sql语句:
CREATE TABLE `employee` (`id` int(4) DEFAULT NULL,`name` varchar(12) DEFAULT NULL,`salary` varchar(11) DEFAULT NULL,`iod` int(4) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8
添加相应数据后得到表employee为:
查询最高薪酬:
SELECT office.`name` as office,employee.`name` as name,employee.salary from office,employee where employee.iod=office.iod and employee.salary=(SELECT MAX(salary) from employee WHERE employee.iod=office.iod); #没有分组,避免了多人具有相同最高工资只显示一人的情况
得到结果为:
查看薪酬分组和百分比情况:
select (case when salary<3000 then '小于3000'when salary>=3000 and salary<5000 then '大于等于3000小于5000'else '大于等于5000' end) as levels, count(salary) as Count, concat(convert(count(salary)*100/(select count(id) from employee),decimal(4,1)),'%') as persentage from employee GROUP BY levels ;#concat--连接字符串 #convert--参数分别可以表示( 要转换到的类型, 合法的表达式, 格式化类型) #decimal(a,b) a--代表精度 b--代表小数点位数
结果如图: