订单三张表
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';