MySQL语句COUNT SUM与IF WHEN组合使用范例 

 1  /* COUNT和IF结合使用 true和null可换成1和0 当orders_status=5时count加1 否则 加0 */
 2  SELECT 
 3  　　`sales_account` , 
 4  　　sum( `orders_total` ) , 
 5 　　 count( `erp_orders_id` ) , 
 6  　　count( if( `orders_status` =5,TRUE , NULL ) ) AS sumtotal
 7  FROM 
 8 　　　`erp_orders`
 9  WHERE
10 　　`orders_export_time` > '2015-10-01 00:00:14'
11  GROUP BY 
12 　　`sales_account`
/*SUM与IF结合使用 当条件成立则求和 否则加0*/
 1 SELECT
 2     n.`sku`,n.`amtoneweek`,n.`amttwoweek`,
 3     (n.`amtoneweek` - n.`amttwoweek`) 'updownamount',
 4     CONCAT(ROUND((n.`amtoneweek` - n.`amttwoweek`)/IF(n.`amttwoweek` = '',1,n.`amttwoweek`) * 100 , 2) , '%') 'updownrange'
 5 FROM(
 6     SELECT
 7         `sku`,
 8         ROUND(SUM(IF(`paytime` > '{$OneWeek}' , `amt` , 0)) , 2) 'amtoneweek',
 9         ROUND(SUM(IF(`paytime` <= '{$OneWeek}' , `amt` , 0)) , 2) 'amttwoweek'
10     FROM
11         `skusales`
12     WHERE
13         `paytime` >= '{$TwoWeek}'
14     AND
15         `paytime` <= '{$search_time}'
16     GROUP BY
17         `sku`
18     ) n
19 WHERE
20         1            
/*SUM与CASE WHEN AND THEN ELSE END结合使用 当(WHEN)两个条件都成立时,(THEN)累加1,(ELSE)否则累加0*/
1 SELECT
2     SUM(CASE WHEN channel_type=1 AND  check_status=6  THEN  1  ELSE  0 END)
3 FROM 
4     tougao_record 
5 WHERE 
6     accept_company_id=100

IF 其实是mysql的一个流程控制函数,接受三个参数, 当第一个参数的结果为真的时候返回第二个参数, 否则返回第三个参数

 

posted @ 2016-06-24 15:58  bky2317894314  阅读(725)  评论(0)  编辑  收藏  举报