MYSql 双条件groupby mysql多条件分组查询

转载

mob64ca140d96d9 2024-06-11 17:36:36

文章标签 MYSql 双条件groupby mysql 数据库字段分组函数 文章分类 MySQL 数据库

一、常见函数

`crm_banner``edu_subject`/*
select 函数名(实参列表) from 表名

单行函数：作处理的
	concat()，length(),ifnull()
分组函数：作统计的（参数多个值，返回一个值）
	
*/
# 一、字符函数
# 1. length() 获取参数字符的字节数
SELECT LENGTH('join');
SELECT LENGTH('张三丰');# 一个汉字占三个字节，UTF-8中，根据字符集的不同，所占字节不同

# 2. concat() 拼接字符串
SELECT CONCAT(first_name,'_',last_name) FROM employees;

# 3. upper、lower 大小写转换
SELECT UPPER('join');
SELECT LOWER('JOIN');

# 4. substr、substring 字符截取 
SELECT SUBSTR('李莫愁爱上了陆展元',6) ouput; # 索引从1开始  两个参数，从指定位置开始截取到最后
SELECT SUBSTR('李莫愁爱上了陆展元',6,1) ouput; # 三个参数，从指定位置开始截取几个字符

# 5. instr  返回子串的起始索引
SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') output;

# 6. trim 去掉首位空格
SELECT TRIM('  张翠山  ');
SELECT TRIM('a' FROM 'aaa小红aa小蓝aaa'); # 去掉首位指定的字符

# 7. lpad  从给定字符前面填充指定字符，直到满足指定的字符数
SELECT LPAD('小黑',10,'*');

# 8. rpid  同上，右填充
# 9. replace
SELECT REPLACE('张无忌爱上了周芷若周芷若周芷若','周芷若','赵敏');


# 二、数学函数
# 1. round() 四舍五入
SELECT ROUND(1.2)
SELECT ROUND(4.567,2) # 小数点后保留两位

# 2. ceil 向上取整
SELECT CEIL(1.01)

# 3. floor 向下取整
SELECT FLOOR(1.9);

# 4. truncate  截断
SELECT TRUNCATE(1.5865,2) # 小数点后保留几位

# 5. mod 取余
SELECT MOD(10,3);


# 三、日期函数
# 1. now() 获取当前系统时间
SELECT NOW();

# 2. curdate() 当前日期，不包含时间
SELECT CURDATE();

# 3. curtime() 当前时间，不包含日期
SELECT CURTIME();

# 4. 可以获取指定的部分：年，月，日，时分秒
SELECT YEAR(NOW());
SELECT YEAR('1998-11-25');
SELECT MONTHNAME(NOW()); # 英文显示

# 5. str_to_date() 将日期类型的字符转换为指定格式的日期
SELECT STR_TO_DATE('1986-6-8','%Y-%c-%d');

# 6.  date_format 将日期转换为字符
SELECT DATE_FORMAT(NOW(),'%c %d %Y');

# 四、其他函数
# 版本号
SELECT VERSION();
SELECT DATABASE();
SELECT USER();

# 五、流程控制函数
# 1. if函数
SELECT IF(2>1,8,20); # 类似于三元运算符
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金','有奖金') 备注 FROM employees;
# 2. case函数 ： switch  case
/*
case 要判断的字段或者表达式
when 常量1 then 要显示的值1或者语句1;
when 常量2 then 要显示的值2或者语句2;
... 
else 要显示的值n或者语句
end
*/
/*
案例：查询员工的工资
部门编号=30的，1.1倍工资
=40的，1.2倍
=50的，1.3倍
其他的，

*/
SELECT salary 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary 
END AS 新工资
FROM employees;

/*
case 的使用情况二：类似于多重if

case
when 条件一 then 要显示的值1或者语句一;
when 条件一 then 要显示的值2或者语句二;
...
else 要显示的值n或者语句n

#案例：查询员工的工资的情况
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000，显示C级别
否则，显示D级别
*/
SELECT salary 工资,
CASE
WHEN salary > 20000 THEN 'A'
WHEN salary > 15000 THEN 'B'
WHEN salary > 10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;

案例：

MYSql 双条件groupby mysql多条件分组查询_数据库

# 1. 显示系统时间(注：日期+时间)
SELECT NOW();

# 2. 查询员工号，姓名，工资，以及工资提高百分之 20%后的结果（new salary）
SELECT employee_id,last_name,salary,salary*1.2 'new salary'
FROM employees;

# 3. 将员工的姓名按首字母排序，并写出姓名的长度（length）
SELECT last_name,LENGTH(last_name),SUBSTR(last_name,1,1) 首字符 
FROM employees
ORDER BY 首字符;

/*
4. 做一个查询，产生下面的结果
<last_name> earns <salary> monthly but wants <salary*3>
Dream Salary
King earns 24000 monthly but wants 72000
*/
SELECT CONCAT(last_name,' earns ',salary,' monthly but wants ', salary*3) AS 'Dream Salary'
FROM employees;

/*
 5. 使用 CASE-when，按照下面的条件：
job grade
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLERK E
产生下面的结果
Last_name Job_id Grade
king AD_PRES A
*/
SELECT last_name,job_id,
CASE job_id
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
WHEN 'SA_REP' THEN 'D'
WHEN 'ST_CLERK' THEN 'E'
END AS 'Grade'
FROM employees;

二、分组函数

#二、分组函数
/*
功能：用作统计使用，又称为聚合函数或统计函数或组函数

分类：
sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数

特点：
1、sum、avg一般用于处理数值型
   max、min、count可以处理任何类型
2、以上分组函数都忽略null值

3、可以和distinct搭配实现去重的运算

4、count函数的单独介绍
一般使用count(*)用作统计行数

5、和分组函数一同查询的字段要求是group by后的字段

*/
#1、简单 的使用
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary) FROM employees;

SELECT SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数
FROM employees;

SELECT SUM(salary) 和,ROUND(AVG(salary),2) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数
FROM employees;

#2、参数支持哪些类型
SELECT SUM(last_name) ,AVG(last_name) FROM employees;  # 字符型转换成0
SELECT SUM(hiredate) ,AVG(hiredate) FROM employees;

SELECT MAX(last_name),MIN(last_name) FROM employees;  # 按排序

SELECT MAX(hiredate),MIN(hiredate) FROM employees;

SELECT COUNT(commission_pct) FROM employees; # count支持任意类型  只计算不是null的
SELECT COUNT(last_name) FROM employees;

#3、是否忽略null
SELECT SUM(commission_pct) ,AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107 FROM employees; # null加任何值都为null，说明null没参与运算

SELECT MAX(commission_pct) ,MIN(commission_pct) FROM employees;

SELECT COUNT(commission_pct) FROM employees;
SELECT commission_pct FROM employees;

#4、和distinct搭配

SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;

SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;


#5、count函数的详细介绍

SELECT COUNT(salary) FROM employees;


SELECT COUNT(*) FROM employees;

SELECT COUNT(1) FROM employees;

效率：
MYISAM存储引擎下  ，COUNT(*)的效率高
INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)要高一些


#6、和分组函数一同查询的字段有限制

SELECT AVG(salary),employee_id  FROM employees;

案例：

# 1. 查询公司员工工资的最大值，最小值，平均值，总和
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees;
# 2. 查询员工表中的最大入职时间和最小入职时间的相差天数 （DIFFRENCE）
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate))
FROM employees;
# 3. 查询部门编号为 90 的员工个数
SELECT COUNT(*)
FROM employees
WHERE department_id = 90;

三、分组查询

#进阶5：分组查询

/*
语法：

select 查询列表
from 表
【where 筛选条件】
group by 分组的字段
【order by 排序的字段】;

特点：
1、和分组函数一同查询的字段必须是group by后出现的字段
2、筛选分为两类：分组前筛选和分组后筛选
		针对的表			位置		连接的关键字
分组前筛选	原始表		group by前	where
	
分组后筛选	group by后的结果集    group by后	having

问题1：分组函数做筛选能不能放在where后面
答：不能

问题2：where——group by——having

一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率

3、分组可以按单个字段也可以按多个字段
4、可以搭配着排序使用

*/
#引入：查询每个部门的员工个数

SELECT COUNT(*) FROM employees WHERE department_id=90;
#1.简单的分组

#案例1：查询每个工种的员工平均工资
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

#案例2：查询每个位置的部门个数
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;

#2、可以实现分组前的筛选

#案例1：查询邮箱中包含a字符的 每个部门的平均工资
SELECT AVG(salary),email,department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;


#案例2：查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;


#3、分组后筛选

#案例：查询哪个部门的员工个数>5
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>5;

#案例2：每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT job_id,MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary) > 12000;


#案例3：领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资

manager_id>102

SELECT manager_id,MIN(salary)
FROM employees 
WHERE manager_id > 102
GROUP BY manager_id
HAVING MIN(salary)>5000;

#4.添加排序

#案例：每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序
SELECT job_id,MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary) > 6000
ORDER BY salary;



#5.按多个字段分组

#案例：查询每个工种每个部门的最低工资,并按最低工资降序
SELECT MIN(salary),job_id,department_id
FROM employees
GROUP BY job_id,department_id
ORDER BY MIN(salary);

案例：

# 1. 查询各 job_id 的员工工资的最大值，最小值，平均值，总和，并按 job_id 升序
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary),job_id 
FROM employees
GROUP BY job_id
ORDER BY job_id;

# 2. 查询员工最高工资和最低工资的差距（DIFFERENCE）
SELECT MAX(salary)-MIN(salary) 'different' FROM employees;

# 3. 查询各个管理者手下员工的最低工资，其中最低工资不能低于 6000，没有管理者的员工不计算在内
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary)>6000;
# 4. 查询所有部门的编号，员工数量和工资平均值,并按平均工资降序
SELECT department_id,COUNT(*),AVG(salary)
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC;
# 5. 选择具有各个 job_id 的员工人数
SELECT COUNT(*) , job_id
FROM employees
GROUP BY job_id;

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。