首页 > 代码库 > MySQL条件判断处理函数_20160925

MySQL条件判断处理函数_20160925

2024-08-14 18:25:38   218人阅读

MySQL条件判断处理

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

首先先将销售分组 

1 SELECT *,2 CASE3 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 a12 ) AS b13 GROUP BY 销售分组

  技术分享

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

1 SELECT city,SUM(pay_money) AS 业绩,2 CASE WHEN SUM(pay_money)<=500 THEN "(0,500]"3 WHEN SUM(pay_money)>500 AND SUM(pay_money)<=1000 THEN "(500,1000]"4 WHEN SUM(pay_money)>1000 AND SUM(pay_money)<=4000 THEN "(1000,4000]"5 ELSE "(4000,3000+)" END AS "业绩区间"6 FROM test_a03order AS a7 GROUP BY city8 ORDER BY SUM(pay_money) DESC

 技术分享

MySQL条件判断处理函数_20160925


联系
我们