MySQL conditional judgment processing function _20160925

MySQL Conditional judgment processing

First, if I want to divide the salesperson into 5 groups, calculate the performance of each sales group

First, group the sales

1 SELECT *,2  Case3  whenSalespersoninch("Ding Test", "Ho Test", "Liu Test", "leaf Test", "Summer Test") Then"Sales Group"4  whenSalespersoninch("Yao Test", "Cui Test", "Zhang Test", "Xu Test", "Cao Test") Then"Sales two Group"5  whenSalespersoninch("Li test", "Yang Test", "King Test", "Stone Test", "Millet Test") Then"Sales three Group"6  whenSalespersoninch("Hu Test", "Xue Test", "Talk Test", "Deng Test", "Qiu Test") Then"Sales four groups"7  whenSalespersoninch("Han test", "Horse Test", "Wei Test") Then"Sales five Group"8 ELSE NULL END  asSales Grouping9  fromTest_a03order asA

aggregating based on the generated Sales grouping field

1 SELECTSales group,SUM(Pay_money) asPerformance2  from (3     SELECT *,4      Case5      whenSalespersoninch("Ding Test", "Ho Test", "Liu Test", "leaf Test", "Summer Test") Then"Sales Group"6      whenSalespersoninch("Yao Test", "Cui Test", "Zhang Test", "Xu Test", "Cao Test") Then"Sales two Group"7      whenSalespersoninch("Li test", "Yang Test", "King Test", "Stone Test", "Millet Test") Then"Sales three Group"8      whenSalespersoninch("Hu Test", "Xue Test", "Talk Test", "Deng Test", "Qiu Test") Then"Sales four groups"9      whenSalespersoninch("Han test", "Horse Test", "Wei Test") Then"Sales five Group"Ten     ELSE NULL END  asSales Grouping One      fromTest_a03order asa A) asb - GROUP  bySales grouping

 

Second, I would like to group the urban performance of the amount of 500 or less divided into a group, the amount of 500 to 1000 of a group of 1000 to 4000 of a group of more than 4000 of a group

1 SELECTCitySUM(Pay_money) asPerformance,2  Case  when SUM(Pay_money)<= -  Then"(0, -]"3  when SUM(Pay_money)> -  and SUM(Pay_money)<= +  Then"( -, +]"4  when SUM(Pay_money)> +  and SUM(Pay_money)<=4000  Then"( +,4000]"5 ELSE"(4000, the+)"END  as"Performance Zone"6  fromTest_a03order asa7 GROUP  by City8 ORDER  by SUM(Pay_money)DESC 

MySQL conditional judgment processing function _20160925