订单三张表

select * from nr_order where order_number ='XT808092014328937' ;
select * from nr_order_product where order_id =4134271;
SELECT * from nr_order_option where order_product_id = 3212354;

根据产品ID查询SKU

SELECT * FROM nr_product_options WHERE goods_id = '119157';

根据订单编号查询商品ID

SELECT * from nr_order WHERE order_number='NR804061817328950';

根据订单ID查询操作记录

SELECT * FROM nr_sys_log WHERE table_id =2841073 ORDER BY id asc;

根据商品ID查询话费填写日期

SELECT * FROM nr_product_spend where product_id =103112997 ORDER BY spenddate desc;

查询物流订单状态

select logistics_id,track_status,count(*) from nrshop.nr_order
 where addtime>'2018-03-01' and addtime<'2018-04-18' and order_status in(3,4,5,6,7) and track_status=0 group by logistics_id;

物流状态汇总

select logistics_id,b.`name`,count(*) from nrshop.nr_order a,nr_sys_area b
 where addtime>'2018-04-01' and addtime<'2018-04-18' and a.area_id=b.id 
and order_status in(3,4,5,6,7) and waybill_number is null group by logistics_id,area_id;

物流订单统计（导给客服）

select b.`name`,a.order_number,a.addtime,c.name from nrshop.nr_order a,nr_sys_area b,nr_sys_currency c
 where addtime>'2018-03-01' and addtime<'2018-04-18' and a.area_id=b.id AND a.currency_id=c.id
and order_status in(3,4,5,6,7) and waybill_number is null ;

查询物流公司

select * from nr_logistics_company
SELECT * FROM nr_order WHERE addtime >= '2018-03-01 00:00:00' and addtime < '2018-03-28 00:00:00' and main_id =103110966 and befrom = 'facebook' and order_status in (3,4.5.6);

根据商品ID查询订单

SELECT * from nr_order where main_id = 103112997;

查询stripe支付失败

SELECT * FROM `nr_stripe_log` where content like "%XJ808301605025138%";

查询优化师商品关联

SELECT * FROM nr_product_ader WHERE user_id=1299

查询最近1000条数据，从1开始

SELECT * FROM nr_product_copy1 LIMIT 0, 1000

根据域名找商品

select * FROM mysql.slow_log WHERE start_time>'2018-04-11 15:57:36' and sql_text like 'select status,id,domain%'

商品编号，订单号，产品名称，出货单名称

SELECT p.sku,`o`.`order_number`,`op`.`goods_name`,`op`.`name`,o.addtime FROM `nr_order` `o` 
LEFT JOIN `nr_order_product` `op` ON `op`.`order_id`=o.id AND o.main_id = op.product_id INNER JOIN nr_product p ON op.product_id = p.id
WHERE  addtime >= '2018-03-01 00:00:00' and addtime < '2018-04-01' and o.area_id =17 and order_status in (3,4,5,6)  GROUP BY o.order_number;

产品名称 出货单名称 产品id

select p.product_name,g.id,g.goods_name from nr_product p INNER JOIN nr_goods g ON p.goods_id = g.id where p.status = 1 and p.area_id = 17;

查询4月份港澳台团队的订单总量

select count(*) from nr_order where currency_id = 1 and area_id = 17 and order_status in (3,4,5,6) and addtime > '2018-03-31' and addtime < '2018-05-01';

查询状态为3456

SELECT * FROM nr_order WHERE order_status in(3,4,5,6);

根据产品分类，查询泰国团队的邮箱

select main_id,cate_id,ship_email,nr_order.area_id,nr_product.id as id from nr_order,nr_product where main_id in( SELECT main_id FROM `nr_order` where nr_order.area_id = 13 GROUP BY main_id) AND nr_order.area_id=13 and (nr_product.id=main_id OR nr_product.goods_id=main_id) and nr_order.ship_email!='' ORDER BY nr_order.main_id DESC limit 100;

根据产品分类，查询泰国团队的邮箱（毛哥优化）

select d.name 分类,c.goods_name 产品名称, b.product_name as 商品名称,ship_email 邮箱  from nr_order a,nr_product b, nr_goods c, nr_product_category d where a.area_id=13 and b.id=a.main_id and b.goods_id=c.id and d.id=c.category_id and a.ship_email like '%@%'

查询某个团队的花费（美金）

SELECT sum(amount)*0.1579 from nr_product_spend where product_id in (select id from nr_product where area_id in (14,16,19,20,23) and `status`=1) and spenddate >= '2018-04-01' and spenddate <= '2018-04-30';

樊帅让查（1）

select * from (
select a.id,a.product_name,d.`name`,CONCAT(a.domain,'/',a.seo_url) as url,e.`name` as designer from nr_product a 
LEFT JOIN nr_goods c on a.goods_id =c.id LEFT JOIN nr_product_category d on c.category_id=d.id 
LEFT JOIN nr_admin e on a.designer_id=e.id
where a.`status`=1 and a.area_id=17 and a.create_time>'2018-04-01' and not exists(
    select 1 from nr_product_ad f, nr_product g where f.product_id=g.id and f.yahoo is not null and f.yahoo<>'' and g.goods_id=c.id
)) a LEFT JOIN
(select main_id, count(*) 订单数 from nr_order where addtime>'2018-04-01' and order_status in(3,4,5,6) group by main_id) b
on a.id=b.main_id ; 

樊帅让查（2）

select * from (
select a.id,a.product_name,d.`name`,CONCAT(a.domain,'/',a.seo_url) as url,e.`name` as designer from nr_product a 
LEFT JOIN nr_goods c on a.goods_id =c.id LEFT JOIN nr_product_category d on c.category_id=d.id 
LEFT JOIN nr_admin e on a.designer_id=e.id
where a.`status`=1 and a.area_id=17 and a.create_time>'2018-04-01' and not exists(
    select 1 from nr_product_ad f, nr_product g where f.product_id=g.id and (
(f.line is not null and f.line<>'') or (f.line2 is not null and f.line2<>'')) and g.goods_id=c.id
)) a LEFT JOIN
(select main_id, count(*) 订单数 from nr_order where addtime>'2018-04-01' and order_status in(3,4,5,6) group by main_id) b
on a.id=b.main_id ; 

我写

SELECT distinct nr_product.id,domain,designer_id,nr_admin.name,nr_product.area_id,nr_goods.category_id,nr_product_category.name
FROM nr_product left JOIN nr_product_ad on nr_product.id=nr_product_ad.product_id left JOIN nr_admin on nr_product.designer_id=nr_admin.id
LEFT JOIN nr_goods ON nr_product.goods_id=nr_goods.id LEFT JOIN nr_product_category on nr_goods.category_id=nr_product_category.id
WHERE nr_product.area_id=17 AND nr_product_ad.yahoo='' 

供应链

select IFNULL(b.SKU,a.goods_sku)AS SKU, c.purchase_price as 采购单价,if(gbuyer=0 or gbuyer is null,buyer, d.`name`) as 采购员,
       a.quantity as 采购数量,getSKU(b.options_value) as 规格名称,  a.order_number as 订单编号,a.addtime as 下单时间,a.goods_id as 产品id, a.cate_name 产品分类,
       a.goods_name as 产品名称, a.area as 团队,a.currency as 币种,a.upt,a.pro_id as 商品id from 
(select b.product_id as pro_id,a.order_number,a.begroup,b.goods_id,sum(IFNULL(c.quantity, b.quantity)) as quantity,c.poid,d.seller_id,d.goods_name,
       d.goods_sku,d.is_import_erp, d.has_sku,e.name, f.name as cate_name,d.buyer as gbuyer,f.buyer ,n.`name` as area, m.`name` as currency, a.addtime as addtime,a.update_time as upt
              from nr_order a 
                INNER JOIN nr_order_product b on a.id=b.order_id 
        INNER JOIN nr_goods d on b.goods_id=d.id
        INNER JOIN nr_sys_area e on a.area_id=e.id
        LEFT JOIN nr_product_category f on d.category_id=f.id
        LEFT JOIN nr_order_option c on b.id = c.order_product_id
                LEFT JOIN nr_sys_area n on a.area_id=n.id
                LEFT JOIN nr_sys_currency m on a.currency_id=m.id
   where a.addtime > '2018-06-01 00:00:00' AND a.addtime < '2018-06-13 00:00:00'
        and a.order_status in(4,5,6) AND e.id =17 and a.order_number='NR806051408168988'
   group by a.id,b.goods_id, c.poid
) a LEFT JOIN nr_product_options b on a.poid=b.poid 
    LEFT JOIN nr_admin d on d.id=a.gbuyer
    LEFT JOIN nr_product_seller_relate c on a.goods_id=c.goods_id and a.seller_id=c.seller_id ;

---

有权限查看港澳台订单的登陆时间及ip

select a.name,l.createtime,l.loginip FROM nr_admin as a inner join nr_admin_login as l on a.id=l.uid WHERE a.area_value=17 and group_id in (9,11,12,13,16,17,25,26,28,29,31,32,33,36,37,38,39) and createtime >= '2018-05-21' and createtime <= '2018-06-21';

兼总条贯 知至知终