xyzshop/document/static_order.sql

#统计每个月的财务报表

SELECT order_sn,pay_sn,buyer_id,buyer_name,FROM_UNIXTIME(payment_time,'%Y-%d-%m'),order_amount,pd_amount,order_amount-pd_amount,payment_code,trade_no
from lrlz_order WHERE order_state in (20,30,40)
                      AND refund_state = 0
                      AND payment_time > unix_timestamp(DATE('2016-07-01'))
                      AND payment_time < unix_timestamp(DATE('2016-08-01'));

#统计单月订单
SELECT sum(order_amount),count(*)
from lrlz_order WHERE order_state in (20,30,40)
                      AND refund_state = 0
                      AND payment_time > unix_timestamp(DATE('2016-07-01'))
                      AND payment_time < unix_timestamp(DATE('2016-08-01'));

#统计每单红包占比情况
SELECT order_amount,pd_amount,pd_amount/order_amount
from lrlz_order WHERE order_state in (20,30,40)
                      AND refund_state = 0
                      AND payment_time > unix_timestamp(DATE('2016-12-01'))
                      AND payment_time < unix_timestamp(DATE('2017-01-01')) AND order_amount > 0.10 ORDER BY order_amount DESC ;

#统计各个品牌的数量

SELECT count(distinct CONCAT(goods_commonid)),brand_name AS nc_count
FROM lrlz_goods as g,lrlz_brand as b
WHERE g.brand_id = b.brand_id and g.goods_state = '1' AND g.goods_verify = '1'
group by g.brand_id

#统计库存总价和卖出的商品总价
SELECT sum(goods_salenum*goods_marketprice),sum(goods_storage * goods_marketprice) FROM `lrlz_goods` WHERE  goods_state = '1' AND goods_verify = '1'

#统计当天新增用户数
SELECT COUNT(*) FROM lrlz_member WHERE member_time > UNIX_TIMESTAMP('2016-08-12');
SELECT COUNT(*) FROM lrlz_member WHERE member_time > UNIX_TIMESTAMP('2016-08-11') AND member_time < UNIX_TIMESTAMP('2016-08-12') ;
SELECT count(*) FROM lrlz_member;


#检查商品和品牌关联


#统计红包使用情况
SELECT * FROM lrlz_user_bonus WHERE user_id = 39377 ORDER BY bonus_id DESC ;

SELECT * FROM lrlz_user_bonus WHERE bonus_status = 3 and usable_time >  UNIX_TIMESTAMP(DATE('2017-09-28 00:00:00')) AND bonus_rate > 30;
SELECT sum(remain_amount) FROM lrlz_user_bonus WHERE bonus_status = 3 and usable_time >  UNIX_TIMESTAMP(DATE('2017-09-28 19:00:00')) AND bonus_rate = 40;

#统计订单毛利和净利润情况
select sum((p.purchase_price)*g.goods_num) as cost,sum(g.goods_num),sum(p.purchase_price),sum(g.goods_price),o.order_amount,o.pd_amount, (o.order_amount - o.pd_amount - sum((purchase_price)*goods_num)) as retained,
  (o.order_amount - o.pd_amount) / o.order_amount as rate,g.buyer_id
from lrlz_order_goods g
left join lrlz_goods_orgprice p
  on g.goods_id=p.goods_id
left join lrlz_order o
  on o.order_id=g.order_id
where o.payment_time
BETWEEN UNIX_TIMESTAMP(DATE('2017-09-01 00:00:00')) and UNIX_TIMESTAMP(DATE('2017-09-28 00:00:00')) and o.order_state in (20,30,40) group by o.order_id;