按月份分组查询例子

SELECT SUM(t.commission) commission,SUM(t.payment_amount) payment_amount,SUM(t.delivery_fee) delivery_fee, SUM(d.transaction_amount) transaction_amount ,DATE_FORMAT(d.add_time,'%Y%m') months 
FROM th_payment_detail d LEFT JOIN company c ON d.cust_id=c.cust_id  LEFT JOIN (SELECT commission,payment_amount,delivery_fee,order_id FROM th_order_list WHERE stock_type = 1) t ON d.order_id = t.order_id WHERE 1=1
AND c.cust_type = 0  GROUP BY months


 SELECT COUNT(t.counts) FROM (SELECT COUNT(1) AS counts
FROM th_payment_detail d LEFT JOIN company c ON d.cust_id=c.cust_id  LEFT JOIN th_order_list t ON d.order_id = t.order_id WHERE 1=1 
AND c.cust_type = 0  GROUP BY DATE_FORMAT(d.add_time,'%Y%m')) t
33


 SELECT d.id,t.commission,t.payment_amount,d.transaction_amount,d.balance_amount,d.order_id,d.type,d.add_time,d.comment,c.cust_name,t. stock_type 
FROM th_payment_detail d LEFT JOIN company c ON d.cust_id=c.cust_id  LEFT JOIN (SELECT commission,payment_amount,stock_type,order_id FROM th_order_list WHERE stock_type = 1) t ON d.order_id = t.order_id WHERE 1=1
AND c.cust_type = 0  


ALTER TABLE `th_order_list` ADD INDEX index_name ( `stock_type` ) 
ALTER TABLE `th_payment_detail` ADD INDEX index_name ( `order_id` ) 
ALTER TABLE `th_order_list` ADD INDEX order_id ( `order_id` ) 




EXPLAIN SELECT d.id,t.commission,t.payment_amount,d.transaction_amount,d.balance_amount,d.order_id,d.type,d.add_time,d.comment,c.cust_name 
FROM th_payment_detail d LEFT JOIN company c ON d.cust_id=c.cust_id LEFT JOIN th_order_list t ON d.order_id = t.order_id AND t.stock_type=1  
WHERE  c.cust_type = '2' AND d.type IN (0,1,2) ORDER BY d.add_time DESC 






SELECT SUM(t.commission) commission,SUM(t.payment_amount) payment_amount,SUM(t.delivery_fee) delivery_fee ,DATE_FORMAT(d.add_time,'%Y%m') months  ,SUM(t1.transaction_amount) zhichu,SUM(t2.transaction_amount) shouru,SUM(t5.transaction_amount) chongzhi,SUM(t3.transaction_amount) tikuan,SUM(t4.transaction_amount) zhifub FROM th_payment_detail d 
LEFT JOIN company c ON d.cust_id=c.cust_id  
LEFT JOIN th_order_list t ON d.order_id = t.order_id AND t.stock_type=1 
 LEFT JOIN th_payment_detail AS t1 ON d.`id`=t1.`id` AND t1.type=0 
 LEFT JOIN th_payment_detail AS t2 ON d.`id`=t2.`id` AND t2.type=1 
 LEFT JOIN th_payment_detail AS t5 ON d.`id`=t5.`id` AND t5.type=2
  LEFT JOIN th_payment_detail AS t3 ON d.`id`=t3.`id` AND  t3.COMMENT ='提款'
   LEFT JOIN th_payment_detail AS t4 ON d.`id`=t4.`id` AND  t4.COMMENT ='支付宝支出'
    WHERE 1=1 AND d.type IN (0,1,2)  AND c.cust_type = 0  GROUP BY months  LIMIT  0,10 








INSERT INTO site_parameter (`PARAM_ID`, `PARAM_NAME`, `PARAM_VALUE`, `PARAM_COMMENT`, `OPER_TIME`, `OPER_USER`) VALUES ('100000000000282', 'zfb_point', '5.5', '支付宝手续费比例', '2016-06-13 15:48:48', '0');