MySQL语句进阶记录（四）

文章简介：

​ 1、使用MySQL方法：find_in_set、over、sum、count、round、greatest

​ 2、适用场景：

​ （1）判断字段是否包含某些字符，亦可以用在表连接的时候判断一个表的字段是否包含另一个表的字段；

​ （2）按照指定字段排序；

​ （3）对字段进行累加操作；

​ （4）保留指定位的小数点；

​ （5）统计字段在某个条件下的查询数量；

​ （6）比较两个字段的大小关系，max是获取一个字段的最大值！！！

1、find_in_set使用

# find_in_set(需要查找的字符串,索引字段) 获取字符串在字段中的位置 一般用于where处，判断字段是否包含某个字符串
SELECT 
    name, belts
FROM
    divisions
WHERE
    NOT FIND_IN_SET('black', belts);

2、from后为子查询的结果，需要给子查询的结果定义一个名称，否则会报错！！！

# 错误写法，报错信息：Every derived table must have its own alias
select first_name
from (
    select 
        first_name,
        row_number() over(order by first_name) as rank_num
    from employees
) 
# 正确写法
select a.first_name
from (
    select 
        first_name,
        row_number() over(order by first_name) as rank_num
    from employees
) a

3、over函数的使用

统计salary的累计和running_total

/**
	OVER([PARTITION BY <列清单> ] ORDER BY <排序用列清单> )
*/
# 结合排序函数使用,结果根据salary降序排序
select
    emp_no,
    salary,
    dense_rank() over(order by salary desc) as t_rank
from salaries
order by t_rank, emp_no;
# 结合sum使用，累加工资
select 
    emp_no,
    salary,
    sum(salary) over(order by emp_no)
from salaries
where to_date = "9999-01-01";

4、sum不仅可以统计数值，还能做一定的判断。count仅仅是统计数量不能做判断。

异常的邮件概率

select
    e.date,
    round(sum(e.type="no_completed")*1.0/count(e.type),3) as p
from 
    email e join user u1 
    on e.send_id = u1.id
    join user u2 on u2.id=e.receive_id
where u2.is_blacklist = 0 and u1.is_blacklist=0
group by e.date
order by e.date;

5、round保留指定小数位

#round(a,b) 对a进行保留b位的小数
# 可看知识点4

6、两个值比较最大值

/**
	greatest(字段名,字段名) 返回较大值的字段名数据
*/
-- 查询单位下的项目状态为3的项目数以及总投资金额
select 
	-- 单位
	pi.construction_unit,	
	-- 单位名下项目状态等于3的数量
	sum(pi.project_status=3) as total_num,
	-- 单位名下项目状态为3的投资总金额
	sum(
		if(pi.project_status=3,
		greatest(ps.investment_rescriptum_statics, ps.investment_rescriptum_dynamics),
		0)
	) as total_investment
FROM
	project_info pi JOIN project_single ps 
	ON pi.evid = ps.project_id 
group by pi.construction_unit;