报表sql（自己打的笔记没有任何逻辑，仅仅为了打笔记而已没有任何参考性）

SELECT id,seller_id,`name`,price,stock,sales,image,is_marketable  
 FROM  t_product where (seller_id =61 and active =1 and is_marketable =1) limit 0, 10

select id as orderId,trade_no,CASE 
WHEN order_status=1 and payment_status=2 and evaluate=0 THEN 1
WHEN order_status=2 AND payment_status=2 AND evaluate=0 AND userr=1 THEN 2
WHEN payment_status=4 or payment_status=5 OR payment_status=6 THEN 3
WHEN order_status=2 AND payment_status=2  THEN 4
END as order_status,if((payment_status=2 AND order_status in (1,2)) ,1,2 ) as refoundble ,total_price,created,updated,evaluate,nickname,phone,
headimgurl,product_img as productImage,stock,sales,name,quantity  from v_commodity where (order_status <>0 and seller_id =61) limit 0, 10

SELECT 
CASE
WHEN is_marketable= 1 THEN 1
WHEN is_marketable= 1 AND stock=0  THEN 2
WHEN is_marketable= 0 THEN 3
END pro_status
FROM t_product WHERE  seller_id=61

SELECT *,SUM(pay_fee) from t_order where created>'2018-09-06'  GROUP BY TO_DAYS(created)

SELECT *,SUM(pay_fee) from t_order where DATE_ADD(created,INTERVAL 1 week)<=NOW() GROUP BY TO_DAYS(created)

SELECT  convert(nvarchar(10),created,120) as Times from t_order


SELECT DATE_FORMAT(created,'%Y-%m-%d') AS days,IFNULL(created,0) from t_order where created>'2018-09-06' GROUP BY days

SELECT *from t_order WHERE created > (SELECT DATE_ADD(MAX(created),INTERVAL -7 DAY) FROM t_order) ORDER BY created DESC;

SELECT  DATE_FORMAT(t2.days,'%m月%d日') edate,IF(SUM(tod.pay_fee) is NULL,0,SUM(tod.pay_fee)) totalPrice
 FROM
(SELECT @cdate := date_add(@cdate,interval-1  day) days from 
(SELECT @cdate := CURDATE() from t_order limit 7) t1) t2 
LEFT JOIN t_order tod ON t2.days=DATE_FORMAT(tod.pay_time,'%Y-%m-%d') 
where 
if(tod.seller_id is NOT NULL,tod.seller_id =61,tod.seller_id is NULL)
and IF(tod.order_status is not NULL,tod.order_status=2,tod.order_status is NULL) 
and IF(tod.payment_status is not NULL,tod.payment_status=2,tod.payment_status is NULL)
GROUP BY t2.days 



SELECT pay_time-1 from t_order WHERE pay_time is not NULL LIMIT 1



#7天平均收益 
SELECT ROUND((SUM(pay_fee)/7),2)  from t_order WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY)<=DATE(pay_time) AND payment_status=2 AND order_status=2


SELECT DATE_FORMAT(t2.days,'%m月%d日') edate,  IF(SUM(tod.pay_fee) is NULL,0,SUM(tod.pay_fee)) totalPrice,COUNT(tod.pay_fee)
 FROM
(SELECT @cdate := date_add(@cdate,interval-1  day) days from 
(SELECT @cdate := CURDATE() from t_order limit 31) t1) t2 
LEFT JOIN t_order tod ON t2.days=DATE_FORMAT(tod.pay_time,'%Y-%m-%d') 
where 
if(tod.seller_id is NOT NULL,tod.seller_id =61,tod.seller_id is NULL)
and IF(tod.order_status is not NULL,tod.order_status=2,tod.order_status is NULL) 
and IF(tod.payment_status is not NULL,tod.payment_status=2,tod.payment_status is NULL)
GROUP BY t2.days 

#饼图统计
 #已核销
SELECT COUNT(id)  from t_order WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY)<=DATE(pay_time) AND payment_status=2 AND order_status=2 AND seller_id=61
 #未核销
SELECT *  from t_order WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY)<=DATE(pay_time) AND payment_status=2 AND order_status=1

SELECT COUNT(id) eeewrite  from t_order WHERE DATE_SUB(CURDATE(),INTERVAL 15 DAY)<=DATE(pay_time) AND payment_status=2 AND order_status=1 and seller_id=61

#按月分组查询
select  date_format(pay_time, '%Y-%m')  from 
t_order WHERE `pay_time` BETWEEN '2017-11-02' AND '2018-12-30' group by date_format(pay_time, '%Y-%m');


#按月份分组查询
#已退款金额
SELECT  COUNT(pay_fee) refundNum ,IF(SUM(cash) IS not NULL,SUM(cash),0 ) refundPrice,
CONCAT(YEAR(auxiliary_time),'-',MONTH(auxiliary_time)) AS fuzhu,DATE_FORMAT(auxiliary_time,'%m月') mont
 from t_auxiliary  
LEFT JOIN t_order toi ON 
CONCAT(YEAR(auxiliary_time),'-',MONTH(auxiliary_time))=CONCAT(YEAR(pay_time),'-',MONTH(pay_time)) 
LEFT JOIN t_refund tr ON toi.id=tr.order_id
WHERE if(seller_id is not NULL ,seller_id=61,seller_id is NULL) 
GROUP BY fuzhu ORDER BY fuzhu desc LIMIT 6

SELECT CONCAT(YEAR(auxiliary_time),'-',MONTH(auxiliary_time)) AS fuzhu from t_auxiliary  
LEFT JOIN t_order  ON 
CONCAT(YEAR(auxiliary_time),'-',MONTH(auxiliary_time))=CONCAT(YEAR(pay_time),'-',MONTH(pay_time)) GROUP BY fuzhu

#已退款单数
SELECT  
    SUM(pay_fee),
    CONCAT(YEAR(pay_time),'-',MONTH(pay_time)) AS releaseYearMonth
FROM t_order where seller_id=61
GROUP BY releaseYearMonth LIMIT 6

select count(*) from v_commodity where ((phone ='fg' or nickname ='df' or trade_no ='df') and (order_status <>0 and seller_id =61))