MySQL條件判斷處理函數_20160925

標籤：

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