MySQL条件判断处理函数_20160925

本文介绍了如何使用MySQL的CASE语句对数据进行条件判断并分组,包括按销售员分组计算业绩及按城市业绩区间分组的方法。

MySQL条件判断处理

一、假如我想把salesperson 分成 5组,计算每个销售分组的业绩

首先先将销售分组 

1 SELECT *,
2 CASE
3 WHEN  salesperson IN ("丁test","何test","刘test","叶test","夏test")  THEN "销售一组"
4 WHEN  salesperson IN ("姚test","崔test","张test","徐test","曹test")  THEN "销售二组"
5 WHEN  salesperson IN ("李test","杨test","王test","石test","粟test")  THEN "销售三组"
6 WHEN  salesperson IN ("胡test","薛test","谈test","邓test","邱test")  THEN "销售四组"
7 WHEN  salesperson IN ("韩test","马test","魏test")  THEN "销售五组"
8 ELSE NULL END AS 销售分组
9 FROM test_a03order AS a

根据生成的销售分组字段进行聚合 

 1 SELECT 销售分组,SUM(pay_money) AS 业绩
 2 FROM (
 3     SELECT *,
 4     CASE
 5     WHEN  salesperson IN ("丁test","何test","刘test","叶test","夏test")  THEN "销售一组"
 6     WHEN  salesperson IN ("姚test","崔test","张test","徐test","曹test")  THEN "销售二组"
 7     WHEN  salesperson IN ("李test","杨test","王test","石test","粟test")  THEN "销售三组"
 8     WHEN  salesperson IN ("胡test","薛test","谈test","邓test","邱test")  THEN "销售四组"
 9     WHEN  salesperson IN ("韩test","马test","魏test")  THEN "销售五组"
10     ELSE NULL END AS 销售分组
11     FROM test_a03order AS a
12 ) AS b
13 GROUP BY 销售分组

  

 二、我想对城市业绩进行分组  金额500以下的分为一组,金额500到1000的一组 1000到4000的一组 4000以上的一组

1 SELECT city,SUM(pay_money) AS 业绩,
2 CASE WHEN SUM(pay_money)<=500 THEN "(0000,0500]"
3 WHEN SUM(pay_money)<=1000 THEN "(0500,1000]"
4 WHEN SUM(pay_money)<=4000 THEN "(1000,4000]"
5 ELSE "(4000以上)" END AS "业绩区间"
6 FROM test_a03order AS a
7 GROUP BY city
8 ORDER BY SUM(pay_money) DESC 

 

转载于:https://www.cnblogs.com/Mr-Cxy/p/5899025.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值