xyzshop/daemon/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)
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;


#检查商品和品牌关联