数据查询文档
主要用来记录日常开发过程当中，查询数据统计用到的sql语句，这样针对下次同样需求的时候能够快速获取相关数据
目录
- 1 操作流程
- 2 数据查询
- 2.1 某活动验证码未使用的用户信息
- 2.2 订单生成时间段统计
- 2.3 每天订单生成时间段统计
- 2.4 用户订单数据统计分析
- 2.5 导出指定分类商家数据
- 2.6 查询某段时间活跃用户数
- 2.7 查询某个订单号支付日志
- 2.8 某类商品的使用时间在指定时间段的特定订单状态的数据(财务的要求)
- 2.9 统计重复短信发送记录
- 2.10 特定优惠券组ID下发优惠券总金额
- 2.11 由特定优惠券组ID产生的消费总金额（打开where最后两行注释，不包含某个产品）
- 2.12 特定优惠券组ID,未消费的客户手机号
- 2.13 由特定优惠券组ID产生的消费信息（不包含年票产品）
- 2.14 特定优惠券组ID,未消费的客户信息
- 2.15 前台转商机用户的跟单匹配
- 2.16 某区域的特定活动分类在售产品列表
- 2.17 第三方票务营收详细列表
- 2.18 第三方票务营收汇总
- 2.19 特定分销平台活动售卖数据
- 2.20 统计某时间段内有订单生成活动的已卖出数
- 2.21 提取部分APP客户端用户并记录
- 2.22 某分类下时间段内订单详情
- 2.23 时间段内相关活动产生的交易成功的订单
- 2.24 查询已下发券
- 2.25 特定分类下的活动商家信息
- 2.26 查询某活动下成功订单的发码信息
- 2.27 按验证码拆分退货表
- 2.28 查询第三方完成退货的信息
- 2.29 查询退款信息
- 2.30 查询结算信息（已经不用，使用下面新酒店结算SQL）
- 2.31 负责人为空和在售活动
- 2.32 统计母婴电商订单的收货人省份分布占比
- 2.33 新酒店结算
- 2.34 微信用户注册消费情况，按天
- 2.35 排除测试的交易关闭订单
- 2.36 票务分类
- 2.37 无结算价问题订单
- 2.38 票务查询
- 2.39 优惠券查询
- 2.40 酒店类未绑定商家的活动
- 2.41 优惠券订单
- 2.42 优惠券面额汇总
- 2.43 活动销量转移
- 2.44 批量更新结算价
- 2.45 统计万圣节或圣诞活动数据
操作流程
- 开发同事处理相关的统计结果
- 发送相应的sql语句给DBA同学(yatao.ding@fumubang.com)，邮件标题为:【数据查询】xxxxxxxxxxxx
数据查询
某活动验证码未使用的用户信息
<syntaxhighlight lang="sql">
select exchange_code,(select receiver from fmb_order_info where order_sn=fec.order_sn) as user,
(select mobile from fmb_order_info where order_sn=fec.order_sn) as mobile, (select create_time from fmb_order_info where order_sn=fec.order_sn) as create_time from fmb_exchange_codes as fec
where rec_id in (select fog.rec_id from fmb_order_goods as fog,fmb_order_info as foi where fog.aid=43636
and fog.order_sn=foi.order_sn and foi.order_status in(1,2,5)) and status=0; </syntaxhighlight>
订单生成时间段统计
<syntaxhighlight lang="sql"> select substring(create_time,12,2) as date,count(*) as total from fmb_order_info where create_time>='2014-10-01' group by date order by total desc,date asc; </syntaxhighlight>
每天订单生成时间段统计
<syntaxhighlight lang="sql"> select substring(create_time,1,13) as date,count(*) as total from fmb_order_info where create_time>='2014-11-01' group by date order by total desc; </syntaxhighlight>
用户订单数据统计分析
<syntaxhighlight lang="sql"> select uid, username, (select receiver from fmb_order_info where uid=fu.uid order by order_id desc limit 1) as realname, (select mobile from fmb_order_info where uid=fu.uid order by order_id desc limit 1) as mobile, (select count(distinct(aid)) from fmb_order_info where uid=fu.uid and order_status in(1,2,5)) as succes_hd_num, (select group_concat(distinct(aid) SEPARATOR ';') from fmb_order_info where uid=fu.uid and order_status in(1,2,5)) as succes_hd_list, (select sum(money) from fmb_order_info where uid=fu.uid and order_status in(1,2,5)) as success_pay_total, (select count(distinct(aid)) from fmb_order_info where uid=fu.uid and order_status in(0,3,4,6)) as failure_hd_num, (select group_concat(distinct(aid) SEPARATOR ';') from fmb_order_info where uid=fu.uid and order_status in(0,3,4,6)) as failure_hd_list, (select sum(money) from fmb_order_info where uid=fu.uid and order_status in(0,3,4,6)) as failure_pay_total, from_unixtime(regdate) as reg_time, (select create_time from fmb_order_info where uid=fu.uid order by order_id desc limit 1) as last_order_time, (select order_city_id from fmb_order_info where uid=fu.uid order by order_id desc limit 1) as city_id
from fmb_users as fu where uid in (select distinct(uid) from fmb_order_info) limit 5
</syntaxhighlight>
导出指定分类商家数据
<syntaxhighlight lang="sql"> select s.shop_id,s.shop_name,s.status,s.category_id,s.category_name, (select city_name from fmb_city where city_id=s.city_id) as city_name, (select area_name from fmb_area where area_id=s.area_id) as area_name from fmb_shop s where s.city_id=1 and s.category_id in(232,212,51,217,218,171,173,20,45,70) order by s.category_id asc; </syntaxhighlight>
查询某段时间活跃用户数
<syntaxhighlight lang="sql"> select count(distinct(uid)) from fmb_activity_log where type='view' and ctime>='2014-05-01 00:00:00' and ctime<='2014-05-31 23:59:59' nd uid in (select uid from fmb_users where regdate>=unix_timestamp('2014-05-01 00:00:00') and regdate<=unix_timestamp('2014-05-31 23:59:59')); </syntaxhighlight>
查询某个订单号支付日志
<syntaxhighlight lang="sql"> select log_id,out_trade_no,trade_status,ctype,ret,is_success,ctime from fmb_pay_log where out_trade_no='20140828125247824051' order by ctime asc; </syntaxhighlight>
某类商品的使用时间在指定时间段的特定订单状态的数据(财务的要求)
<syntaxhighlight lang="sql"> select e.city_id 地域,
f.title 活动名称,
a.shop_user_id 商家ID,
a.aid 活动ID,
a.goods_id 票种ID,
b.tname 商品说明,
a.play_time 使用时间,
c.create_time 下单时间,
a.order_sn 订单号,
c.pay_source 支付方式,
c.order_status 订单状态,
d.status 退货状态,
a.goods_price 售价,
b.settle_price 结算价
from fmb_order_goods a
left join fmb_tickets b
on a.goods_id = b.ticket_id
left join fmb_order_info c
on a.order_sn = c.order_sn
left join fmb_order_returns d
on a.rec_id = d.rec_id
left join fmb_group_tickets e
on a.goods_group_id = e.ticket_group_id
left join fmb_new_activity f
on a.aid = f.aid
where a.play_time >= '2014-11-01'
and a.play_time < '2015-01-01'
and a.aid in
(select aid
from fmb_group_tickets e
where cat_id in (40, 26, 41, 22, 42, 50, 49, 45))
and a.order_sn in (select order_sn
from fmb_order_info e
where order_status in (1, 2, 5));
</syntaxhighlight>
统计重复短信发送记录
<syntaxhighlight lang="sql"> select id,count(*) as total from fmb_sms group by (concat(phone,message,m_log_id)) having total>1 order by total desc; </syntaxhighlight>
特定优惠券组ID下发优惠券总金额
<syntaxhighlight lang="sql"> select sum(coupon_money) 优惠券金额
from fmb_user_coupon where send_status = 1 /*派发状态 0为未发送 1为已发送'*/ and coupon_id = 83 /*优惠券组ID*/ and uid is not null; /*优惠券关联到用户ID*/
</syntaxhighlight>
由特定优惠券组ID产生的消费总金额（打开where最后两行注释，不包含某个产品）
<syntaxhighlight lang="sql"> select sum(a.money) 支付金额
from fmb_order_info a
inner join fmb_user_coupon b
on a.order_sn = b.order_sn
where a.order_status in (1, 2, 5) /*'订单状态。0.未付款 1.等待发货 2.已发货 3.退货中 4.已退货 5.交易成功;6.交易关闭7.支付尾款'*/
and b.send_status = 1 /*派发状态 0为未发送 1为已发送'*/
and b.coupon_id = 83 /*优惠券组ID*/
and b.uid is not null /*已分配给用户*/
and b.order_sn <> ; /*优惠券关联订单号*/
/*and a.order_sn in
(select order_sn from fmb_order_goods c where c.aid not in (113581)); 不包含某活动ID的订单*/
</syntaxhighlight>
特定优惠券组ID,未消费的客户手机号
<syntaxhighlight lang="sql"> select mobile 收货人手机
from fmb_user_address
where uid in (select uid
from fmb_user_coupon
where send_status = 1 /*派发状态 0为未发送 1为已发送'*/
and coupon_id = 83 /*优惠券组ID*/
and uid is not null /*已分配给用户*/
and order_sn = ); /*优惠券关联订单号*/
</syntaxhighlight>
由特定优惠券组ID产生的消费信息（不包含年票产品）
<syntaxhighlight lang="sql"> select a.uid 用户uid, e. username 用户昵称, c.goods_id 商品id, d.tname 名称
from fmb_order_info a
inner join fmb_user_coupon b
on a.order_sn = b.order_sn
inner join fmb_order_goods c
on a.order_sn = c.order_sn
inner join fmb_tickets d
on c.goods_id = d.ticket_id
inner join fmb_users e
on a.uid = e.uid
where a.order_status in (1, 2, 5) /*'订单状态。0.未付款 1.等待发货 2.已发货 3.退货中 4.已退货 5.交易成功;6.交易关闭7.支付尾款'*/
and b.send_status = 1 /*派发状态 0为未发送 1为已发送'*/
and b.coupon_id = 83 /*优惠券组ID*/
and b.uid is not null /*已分配给用户*/
and b.order_sn <> /*优惠券关联订单号*/
and a.order_sn in
(select order_sn from fmb_order_goods c where c.aid not in (113581)); /*不包含某活动ID的订单*/
</syntaxhighlight>
特定优惠券组ID,未消费的客户信息
<syntaxhighlight lang="sql"> select a.uid 用户UID, b.username 用户昵称
from fmb_user_address a
inner join fmb_users b
on a.uid = b.uid
where a.uid in (select uid
from fmb_user_coupon
where send_status = 1 /*派发状态 0为未发送 1为已发送'*/
and coupon_id = 83 /*优惠券组ID*/
and uid is not null /*已分配给用户*/
and order_sn = ); /*优惠券关联订单号*/
</syntaxhighlight>
前台转商机用户的跟单匹配
<syntaxhighlight lang="sql"> select a.uid 用户uid,
c.username 用户名,
a.mobile 手机号,
a.order_sn 订单号,
a.money 订单金额,
a.order_status 订单状态码,
case a.order_status
when 0 then
'未付款'
when 1 then
'等待发货'
when 2 then
'已发货'
when 3 then
'退货中'
when 4 then
'已退货'
when 5 then
'交易成功'
when 6 then
'交易关闭'
when 7 then
'支付尾款'
else
end 订单状态,
create_time 单生成时间
from fmb_order_info a
inner join (select distinct mobile mobile from t_t01) b
on a.mobile = b.mobile
and a.mobile <>
left join fmb_users c
on a.uid = c.uid
order by a.uid;
</syntaxhighlight>
某区域的特定活动分类在售产品列表
<syntaxhighlight lang="sql"> SELECT e.aid 活动ID,
(select f.title from fmb_new_activity f where e.aid = f.aid) 活动名称,
b.shop_user_id 商家ID,
b.ticket_id 票种ID,
b.tname 商品说明,
CONCAT('http://www.fumubang.com/h', e.aid, '.html') 链接地址,
d.shop_name 商家名称,
b.goods_price 销售价,
b.settle_price 结算价,
c.real_name 商家联系人,
c.mobile 手机号
FROM fmb_tickets b
LEFT JOIN fmb_shop_users c
ON b.shop_user_id = c.shop_user_id
LEFT JOIN fmb_shop d
ON c.shop_id = d.shop_id
LEFT JOIN fmb_group_tickets e
ON b.ticket_group_id = e.ticket_group_id
WHERE e.cat_id IN
(36, 52, 51, 39, 30, 14, 32, 33, 46, 37, 1, 31, 43, 47, 53)/*fmb_goods_category 亲子活动，演出展览*/
and e.city_id = 1/*北京*/
/*以下条件在售商品*/
and b.is_on_sale = 1
and b.is_delete = 0
and b.tname != '预定金'
and b.number > 0
and ((b.ticket_time > now() and
(b.valid_end_time > now() or unix_timestamp(b.valid_end_time) = 0) and
b.ticket_type = 1) or
((unix_timestamp(b.valid_end_time) - b.sell_limit_time) >
unix_timestamp(now()) and b.ticket_type = 2) or
((b.valid_end_time = '0000-00-00 00:00:00' and b.ticket_type = 3) or
(b.valid_end_time > now() and b.ticket_type = 3)) or
(b.ticket_type = 4))
order by e.aid, b.ticket_id;
</syntaxhighlight>
第三方票务营收详细列表
<syntaxhighlight lang="sql"> select c.third_part,a.order_sn,b.goods_number,b.goods_price,(b.goods_number*b.goods_price) as money,a.order_status from fmb_order_info as a,fmb_order_goods as b,fmb_group_tickets as c where a.order_sn=b.order_sn and b.goods_group_id=c.ticket_group_id and a.order_status in (1,2,5) and c.third_part!= order by c.third_part asc; </syntaxhighlight>
第三方票务营收汇总
<syntaxhighlight lang="sql"> select c.third_part,sum((b.goods_number*b.goods_price)) as money from fmb_order_info as a,fmb_order_goods as b,fmb_group_tickets as c where a.order_sn=b.order_sn and b.goods_group_id=c.ticket_group_id and a.order_status in (1,2,5) and c.third_part!= group by third_part; </syntaxhighlight>
特定分销平台活动售卖数据
<syntaxhighlight lang="sql"> select b.third_part 分销平台名称,
b.aid 活动id,
(select f.title from fmb_new_activity f where b.aid = f.aid) 活动名称,
a.shop_user_id 商家id,
a.ticket_id 票种id,
a.tname 商品说明,
concat('http://www.fumubang.com/h', b.aid, '.html') 链接地址,
a.goods_price 销售价,
a.settle_price 结算价,
(select sum(ifnull(goods_number, 0))
from fmb_order_goods g
where a.ticket_id = g.goods_id
and order_sn in (select order_sn
from fmb_order_info e
where order_status in (1, 2, 5))) 卖出件数
from fmb_tickets a
inner join fmb_group_tickets b
on a.ticket_group_id = b.ticket_group_id
where b.third_part = 'xiaoniren' /*yuanfan,tworld,meijing*/
order by b.aid, a.ticket_id;
</syntaxhighlight>
统计某时间段内有订单生成活动的已卖出数
<syntaxhighlight lang="sql"> select sum(fog.goods_number),fog.aid from fmb_order_goods fog,fmb_order_info foi where fog.aid in
(select distinct(aid) from fmb_order_goods where ctime>='2015-01-14 16:35:00' and ctime<='2015-01-15 08:03:00') and fog.order_sn=foi.order_sn and foi.order_status in(0,1,2,5) group by fog.aid;
</syntaxhighlight>
提取部分APP客户端用户并记录
备库179执行，对表定期进行合并，不影响主库。 <syntaxhighlight lang="sql"> create table tmp_app0119 select distinct a.uid ,b.receiver ,b.mobile from fmb_users a inner join fmb_order_info b on a.uid=b.uid where a.appname='fmb' order by uid,receiver,mobile limit 600;
select uid,receiver 姓名,mobile 手机 from tmp_app0119 order by uid,receiver,mobile; </syntaxhighlight>
某分类下时间段内订单详情
<syntaxhighlight lang="sql"> select distinct a.order_sn 订单号,
a.order_name 订单名称（活动名称）,
c.play_time 游玩时间,
a.money 订单金额,
a.receiver 收货人,
a.mobile 手机号码,
a.create_time 订单时间
from fmb_order_info a, fmb_new_activity b, fmb_order_goods c
where a.order_sn = c.order_sn
and b.aid = c.aid
and a.order_status IN (1, 2, 5)
and b.category_id in (40, 26, 41, 22, 42, 50, 49, 45) /*家庭旅行*/
and (a.create_time BETWEEN '2014-09-01' and '2014-10-08')
order by a.create_time
</syntaxhighlight>
时间段内相关活动产生的交易成功的订单
<syntaxhighlight lang="sql"> select order_sn 订单号, mobile 手机号, money 支付金额
from fmb_order_info where aid in (133721, 115677, 128095, 65671, 136797, 133713) and order_status in (1, 2, 5) and create_time between '2015-1-24 9:17:00' and '2015-1-27 11:30:00' order by create_time;
</syntaxhighlight>
查询已下发券
<syntaxhighlight lang="sql"> select a.coupon_sn 券号,
c.coupon_name 券组名称,
a.coupon_money 面额,
a.min_amount 限额,
a.coupon_start_time 有效期开始,
a.coupon_end_time 有效期结束,
case a.send_status
when 0 then
'未发送'
when 1 then
'已发送'
else
'未知'
end 派发状态,
ifnull(a.uid, '未绑定') 绑定状态,
case a.use_status
when 0 then
'未使用'
when 1 then
'使用中'
when 2 then
'已使用'
when 3 then
'已冻结'
else
'未知'
end 使用状态,
a.order_sn 订单号,
b.money 订单金额
from fmb_user_coupon a
left join fmb_order_info b
on a.order_sn = b.order_sn
left join fmb_coupon_type c
on a.coupon_id = c.coupon_id
where a.send_status = 1;
</syntaxhighlight>
特定分类下的活动商家信息
<syntaxhighlight lang="sql"> select a.aid 活动ID,
b.shop_user_id 商家ID,
a.title 活动名称,
case b.city_id
when 1 then
'北京'
when 2 then
'上海'
else
'未知'
end 地域,
(select cate_name
from fmb_goods_category
where cate_id = (select parentid
from fmb_goods_category
where cate_id = b.cat_id)) 一级分类,
(select cate_name from fmb_goods_category where cate_id = b.cat_id) 二级分类,
c.username 单位名称,
c.real_name 姓名,
c.position 职务,
c.mobile 手机号,
-- qq,
c.email 邮箱,
a.end_time 结束时间,
a.ctime 活动创建时间,
CONCAT('http://www.fumubang.com/h', a.aid, '.html') 链接地址
from fmb_new_activity a
inner join fmb_group_tickets b
on a.aid = b.aid
-- and a.status <> 'shield' -- 过滤屏蔽的
and a.start_time > '2014-06-30'
and b.cat_id in
(40, 26, 41, 22, 42, 50, 49, 45, 36, 52, 51, 39, 30, 14, 32, 33, 46) -- 家庭旅行，亲子活动
and b.city_id = 1 -- 北京
and b.status = 1 -- 过滤屏蔽的
left join fmb_shop_users c
on b.shop_user_id = c.shop_user_id
and c.status = 1 -- 过滤屏蔽的
</syntaxhighlight>
查询某活动下成功订单的发码信息
<syntaxhighlight lang="sql"> select fog.order_sn as order_sn,
fog.goods_number as goods_number,
fog.goods_price as goods_price,
fog.play_time as play_time,
group_concat(fec.exchange_code) as code_list,
(select receiver from fmb_order_info where order_sn=fog.order_sn) as receiver,
(select mobile from fmb_order_info where order_sn=fog.order_sn) as mobile,
(select title from fmb_new_activity where aid=fog.aid) as activity_title,
(select tname from fmb_tickets where ticket_id=fog.goods_id) as tname
from fmb_order_goods as fog,fmb_exchange_codes as fec where fog.order_sn=fec.order_sn and fog.aid=147895 group by order_sn
</syntaxhighlight>
按验证码拆分退货表
原始：------------拆分后： ["1","a,b,c",9]--["1","a",3] ["2","",5]-------["1","b",3] ------------------["1","c",3] ------------------["2","",5]<syntaxhighlight lang="sql">
select a.return_id,
substring_index(substring_index(a.return_codes, ',', b.help_topic_id + 1),',',-1) return_codes,
money / (select length(return_codes) - length(replace(return_codes, ',', )) + 1 from fmb_order_returns i where i.return_id = a.return_id) money
from fmb_order_returns a
join mysql.help_topic b
on b.help_topic_id < (length(a.return_codes) - length(replace(a.return_codes, ',', )) + 1)
order by a.return_id;
</syntaxhighlight>
查询第三方完成退货的信息
<syntaxhighlight lang="sql"> SELECT a.return_id 退货单号,
a.order_sn 退货订单号,
b.use_coupon 订单是否使用优惠券,
e.title 活动名称,
c.third_part 第三方名称,
a.goods_num 退货数量,
-- f.goods_price 商品单价,
(select goods_price from fmb_order_goods t where a.rec_id=t.rec_id) 商品单价,
a.money 退货金额,
b.money 商品总金额,
a.return_time 退货时间,
b.shipping_fee 配送费用,
b.shape_type 电子票实体票
FROM fmb_order_returns a
INNER JOIN fmb_order_info b
ON a.order_sn = b.order_sn
INNER JOIN fmb_group_tickets c
ON b.goods_group_id = c.ticket_group_id
INNER JOIN fmb_new_activity e
ON b.aid = e.aid
INNER JOIN fmb_tickets f
ON a.goods_id = f.ticket_id
WHERE a.STATUS = 2
AND c.third_part <>
</syntaxhighlight>
查询退款信息
<syntaxhighlight lang="sql"> SELECT CASE b.order_city_id WHEN 1 THEN '北京' WHEN 2 THEN '上海' ELSE '未知' END 地域,b.shop_user_id 商家ID, (select t2.username from fmb_shop_users t2 where t2.shop_user_id=b.shop_user_id) 商家名称, b.aid 产品ID,(SELECT title FROM fmb_new_activity t WHERE b.aid=t.aid) 产品名称,a.money 退款的金额,a.reason 退款的理由 FROM fmb_order_returns a INNER JOIN fmb_order_info b
ON a.order_sn = b.order_sn
INNER JOIN fmb_group_tickets c
ON b.goods_group_id = c.ticket_group_id
WHERE a.return_time BETWEEN '2014-10-01' AND '2015-04-01' AND a.STATUS IN (1,2) AND c.cat_id IN (40, 26, 41, 22, 42, 50, 49, 45) /*家庭旅行*/ </syntaxhighlight>
查询结算信息（已经不用，使用下面新酒店结算SQL）
<syntaxhighlight lang="sql"> select a.order_sn 订单号,
date_format(b.play_time, '%Y-%m-%d') 入住日期,
d.market_price 原价,
b.goods_price 现价,
d.settle_price 结算价,
case c.status when 0 then '待审核' when 1 then '审核通过' when 2 then '完成退货' when 3 then '拒绝退货' else '无退货' end 退货状态,
c.money 退货金额,
b.goods_number 房量,
a.money 订单总额,
a.receiver 入住人姓名
from fmb_order_info a
inner join fmb_order_goods b
on a.order_sn = b.order_sn
and b.play_time < '2015-04-16'
left join fmb_order_returns c
on b.rec_id = c.rec_id
inner join fmb_hotel_price d
on b.goods_id = d.ticket_id and
left(b.play_time, 10) = d.target_date
where a.aid in (156873)
and a.pay_status = 2
and a.order_status in (5)
group by a.order_sn
</syntaxhighlight>
负责人为空和在售活动
<syntaxhighlight lang="sql"> select a.aid 活动id,
a.title 活动标题,
concat('http://www.fumubang.com/h', a.aid, '.html') 活动网址,
case a.city_id when 1 then '北京' when 2 then '上海' else '未知' end 活动地域,
a.ctime 创建时间
from fmb_new_activity a, fmb_group_tickets g, fmb_tickets t
where t.ticket_group_id = g.ticket_group_id
and a.aid = g.aid
and t.is_on_sale = 1
and t.number > 0
and t.tname != '预定金'
and t.is_delete = 0
and (((unix_timestamp(t.ticket_time) - t.sell_limit_time) >
unix_timestamp(now()) and t.valid_end_time > now() and
t.ticket_type = 1) or
((unix_timestamp(t.valid_end_time) - t.sell_limit_time) >
unix_timestamp(now()) and t.ticket_type = 2) or
((t.valid_end_time = '0000-00-00 00:00:00' and t.ticket_type = 3) or
(t.valid_end_time > now() and t.ticket_type = 3) or
(t.ticket_type = 4))) > 0
and a.manager =
group by a.aid
order by a.ctime
</syntaxhighlight>
统计母婴电商订单的收货人省份分布占比
<syntaxhighlight lang="sql"> select a.province 省份id,b.`name` 省份,count(1) 订单数,sum(a.money) 总金额 from fmb_order_info a left join jishigou_common_district b on a.province=b.id where a.goods_group_id in(select ticket_group_id from fmb_group_tickets where cat_id in(38,34,35,44)/*精品购物*/ and ticket_type=3/*通用*/) and a.shape_type=2 /*实体票*/ and a.order_status in(1,2,5) /*1.等待发货 2.已发货 5.交易成功*/ group by province </syntaxhighlight>
新酒店结算
<syntaxhighlight lang="sql"> SELECT a.order_sn 订单号,CASE a.order_status WHEN 5 THEN '交易成功' WHEN 6 THEN '交易关闭' WHEN 7 THEN '支付尾款' END 订单状态, IF(ifnull(change_to_order,0)>0,concat('已改签',change_to_order), IF((SELECT COUNT(1) FROM fmb_order_info WHERE change_to_order=a.order_sn)>0, concat((SELECT order_sn FROM fmb_order_info WHERE change_to_order=a.order_sn LIMIT 1),'改签后'),) ) 改签, date_format(a.create_time, '%Y-%m-%d') 下单时间, group_concat(date_format(b.play_time, '%Y-%m-%d')) 入住日期, group_concat(d.market_price) 原价, group_concat(b.goods_price) 现价, group_concat(d.settle_price) 结算价, group_concat(CASE c.STATUS WHEN 0 THEN '待审核' WHEN 1 THEN '审核通过' WHEN 2 THEN '完成退货' WHEN 3 THEN '拒绝退货' ELSE '无退货' END) 退货状态,/*group_concat(c.status),*/ group_concat(c.money) 退货金额, group_concat(b.goods_number) 房量,a.money 订单总额,a.receiver 入住人姓名 FROM fmb_order_info a INNER JOIN fmb_order_goods b ON a.order_sn=b.order_sn AND b.play_time>='2015-04-16 00:00:00' AND b.play_time<'2015-05-06 00:00:00' LEFT JOIN fmb_order_returns c ON b.rec_id=c.rec_id and c.status=2 INNER JOIN fmb_hotel_price d ON b.goods_id=d.ticket_id AND LEFT(b.play_time,10)=d.target_date WHERE a.aid=157531 AND a.pay_status=2 AND a.order_status IN(5,6,7) GROUP BY a.order_sn ORDER BY b.play_time; </syntaxhighlight>
微信用户注册消费情况，按天
<syntaxhighlight lang="sql"> select date_format(from_unixtime(a.regdate),'%Y-%m-%d') regd,count(distinct a.uid),sum(money) from fmb_users a left join fmb_order_info b on a.uid=b.uid and b.order_status in(1,2,5) and date_format(b.pay_time,'%Y-%m-%d')=date_format(from_unixtime(a.regdate),'%Y-%m-%d') where from_unixtime(a.regdate)>='2015-01-01' and concat(a.weixin_uid,a.weixin_unionid) is not null and concat(a.weixin_uid,a.weixin_unionid)<> group by regd order by regd; </syntaxhighlight>
排除测试的交易关闭订单
<syntaxhighlight lang="sql"> select a.uid 用户ID,c.username 用户名,a.order_sn 订单号,d.tname 商品名称,b.goods_price*b.goods_number 金额,date_format(a.create_time,'%Y-%m-%d') 下单时间 from fmb_order_info a inner join fmb_order_goods b on a.order_sn=b.order_sn inner join fmb_users c/*过滤无头订单*/ on a.uid=c.uid inner join fmb_tickets d on b.goods_id=d.ticket_id where a.order_status=6 and create_time>='2015-01-01' and order_city_id=1 /*北京*/ and a.uid not in(629,92421,103605,170389,280975,364305) /*测试账号*/ order by a.create_time,a.order_sn,b.goods_id;
/*测试账号 select uid from fmb_users where username in('男人来自火星哈哈','测试账号','提莫君','茂茂') or phone_number in('18610188706','13811117893')
- /
</syntaxhighlight>
票务分类
select * from fmb_goods_category where cate_name='演出展览'; select * from fmb_goods_category where cate_name='亲子活动'; select * from fmb_goods_category where cate_name='家庭旅行'; select * from fmb_goods_category where cate_name='精品购物'; 演出展览:37,1,31,43,47,53 亲子活动:36,52,51,39,30,14,32,33,46,55,57 家庭旅行:40,26,41,22,42,50,49,45 精品购物:38,34,35,44
无结算价问题订单
<syntaxhighlight lang="sql"> select i.order_sn 订单号,i.create_time 下单时间, CASE i.order_status WHEN 0 THEN '未付款' WHEN 1 THEN '等待发货' WHEN 2 THEN '已发货' WHEN 3 THEN '退货中' WHEN 4 THEN '已退货' WHEN 5 THEN '交易成功' WHEN 6 THEN '交易关闭' WHEN 7 THEN '支付尾款' ELSE '未知' END 订单状态, CASE r.STATUS WHEN 0 THEN '待审核' WHEN 1 THEN '审核通过' WHEN 2 THEN '完成退货' WHEN 3 THEN '拒绝退货' WHEN 4 THEN '确认收到货' ELSE '无退货' END 退货状态, t.shop_user_id 商家ID, i.aid 活动ID,a.title 活动名称,g.goods_id 票种ID,t.tname 票种名称,g.play_time 使用时间, t.goods_price 现价,g.settle_price 订单结算价,t.settle_price 目前结算价, 实际结算价 from fmb_order_goods g inner join fmb_order_info i on g.order_sn=i.order_sn and i.create_time between '2015-05-19' and '2015-06-17 23:59:59' left join fmb_users u on i.uid=u.uid left join fmb_new_activity a on i.aid=a.aid left join fmb_tickets t on g.goods_id=t.ticket_id left join fmb_order_returns r/*如果存在一对多，不适用*/ on i.order_sn=r.order_sn where g.settle_price=0 /*and g.goods_id not in(select ticket_id from fmb_hotel_price) 是否有酒店的*/ order by i.order_sn,g.goods_id; </syntaxhighlight>
票务查询
<syntaxhighlight lang="sql"> select g.ticket_group_id 票务ID,g.aid 活动ID,g.shop_user_id 商家ID, a.title 活动名称, concat('http://www.fumubang.com/h',g.aid,'.html') 活动链接, date_format(a.ctime,'%Y-%m-%d') 创建时间,date_format(a.end_time,'%Y-%m-%d') 结束时间,a.sell_num 实际已卖出数量, 是否在售, g.third_part 第三方,s.shop_name 关联商家 ,a.status from fmb_group_tickets g inner join fmb_new_activity a on g.aid=a.aid left join fmb_tickets t on g.ticket_group_id=t.ticket_group_id left join fmb_shop_users u on g.shop_user_id=u.shop_user_id left join fmb_shop s on u.shop_id=s.shop_id where g.third_part in('xiaoniren','dadong','yuanfan','meijing','tworld','piaozhijia') group by t.ticket_group_id order by g.aid;
在售条件： where t.is_on_sale=1 and t.is_delete=0 and t.tname !='预定金' and t.number > 0 and g.status=1 and
( ( t.ticket_time > now() and (t.valid_end_time > now() or unix_timestamp(t.valid_end_time)=0) and t.ticket_type=1 ) or ((unix_timestamp(t.valid_end_time)-t.sell_limit_time) > unix_timestamp(now()) and t.ticket_type=2 ) or ((t.valid_end_time='0000-00-00 00:00:00' and t.ticket_type=3) or (t.valid_end_time > now() and t.ticket_type=3 ) ) or (t.ticket_type=4) )
</syntaxhighlight>
优惠券查询
<syntaxhighlight lang="sql"> 优惠券派发张数 优惠券派发总额 优惠券使用张数 优惠券使用总额 使用优惠券订单数 使用优惠券订单总额 运费收入总额 select date_format(distribute_time, '%Y年%m月') 月份,
count(1) 优惠券派发张数,
sum(coupon_money) 优惠券派发总额
from fmb_user_coupon
where send_status = 1 /*派发状态 0为未发送 1为已发送'*/
and uid is not null /*优惠券关联到用户id*/
group by date_format(distribute_time, '%Y-%m') /*派发时间*/
order by date_format(distribute_time, '%Y-%m');
select date_format(use_time, '%Y年%m月') 月份,
count(1) 优惠券使用张数,
sum(coupon_money) 优惠券使用总额
from fmb_user_coupon
where send_status = 1 /*派发状态 0为未发送 1为已发送'*/
and uid is not null /*优惠券关联到用户id*/
and order_sn <> /*优惠券关联订单号*/
and use_status = 2 /*使用状态 0为未使用 1为使用中 2为已使用 3是已冻结*/
group by date_format(use_time, '%Y-%m') /*使用时间*/
order by date_format(use_time, '%Y-%m');
select date_format(b.use_time, '%Y年%m月') 月份,
count(1) 使用优惠券订单数,
sum(a.money) 使用优惠券订单总额
from fmb_order_info a
inner join fmb_user_coupon b
on a.order_sn = b.order_sn
where a.order_status in (1, 2, 5) /*'订单状态。0.未付款 1.等待发货 2.已发货 3.退货中 4.已退货 5.交易成功;6.交易关闭7.支付尾款'*/
and b.send_status = 1 /*派发状态 0为未发送 1为已发送'*/
and b.uid is not null /*已分配给用户*/
and b.order_sn <> /*优惠券关联订单号*/
and b.use_status = 2 /*使用状态 0为未使用 1为使用中 2为已使用 3是已冻结*/
group by date_format(b.use_time, '%Y-%m') /*使用时间*/
order by date_format(b.use_time, '%Y-%m');
select date_format(a.create_time, '%Y年%m月') 月份,
sum(shipping_fee) 运费收入总额 from fmb_order_info a where a.order_status in (1, 2, 5) /*'订单状态。0.未付款 1.等待发货 2.已发货 3.退货中 4.已退货 5.交易成功;6.交易关闭7.支付尾款'*/ group by date_format(a.create_time, '%Y-%m') /*订单创建时间*/ order by date_format(a.create_time, '%Y-%m');
</syntaxhighlight>
酒店类未绑定商家的活动
<syntaxhighlight lang="sql"> select distinct a.aid,a.title,s.shop_id from fmb_new_activity a inner join fmb_group_tickets gt on a.aid=gt.aid and gt.ticket_type=4 /*酒店类*/ left join fmb_activity_join_shop s on a.aid=s.aid where (s.shop_id= or s.shop_id is null) /*没绑定商家*/ and a.title not like '%测试%' and a.status<>'shield'; /*未屏蔽*/ </syntaxhighlight>
优惠券订单
<syntaxhighlight lang="sql"> SELECT i.order_sn 订单号,group_concat(DISTINCT IF(gc.parentid=0,gc.cate_name,gc1.cate_name)) 一级分类, /*group_concat(distinct gt.cat_id) 分类ID,group_concat(distinct gc.cate_name) 分类,*/ c.coupon_money 优惠券面额,SUM(g.goods_price*g.goods_number) 商品总额,SUM(g.settle_price*g.goods_number) 结算总价 FROM fmb_order_info i INNER JOIN fmb_order_goods g ON i.order_sn=g.order_sn INNER JOIN fmb_user_coupon c ON i.order_sn=c.order_sn AND c.send_status = 1 /*派发状态 0为未发送 1为已发送'*/ AND c.uid IS NOT NULL /*已分配给用户*/ AND c.order_sn <> /*优惠券关联订单号*/ AND c.use_status IN(1,2) /*使用状态 0为未使用 1为使用中 2为已使用 3是已冻结*/ and c.distribute_time BETWEEN '2015-01-01' AND '2015-06-30 23:59:59' /*派发时间*/ LEFT JOIN fmb_group_tickets gt ON i.goods_group_id=gt.ticket_group_id LEFT JOIN fmb_goods_category gc ON gt.cat_id=gc.cate_id LEFT JOIN fmb_goods_category gc1 ON gc.parentid=gc1.cate_id WHERE /*i.create_time BETWEEN '2015-01-01' AND '2015-06-30 23:59:59' AND */i.order_status IN(1,2,5) /*'订单状态。0.未付款 1.等待发货 2.已发货 3.退货中 4.已退货 5.交易成功;6.交易关闭7.支付尾款'*/ GROUP BY i.order_sn; </syntaxhighlight>
优惠券面额汇总
<syntaxhighlight lang="sql"> select coupon_money 优惠券面额,min_amount 使用限额,count(coupon_sn) 计数 from fmb_user_coupon where distribute_time between '2015-01-01' and '2015-06-30 23:59:59' and send_status = 1 /*派发状态 0为未发送 1为已发送'*/ group by coupon_money,min_amount; </syntaxhighlight>
活动销量转移
<syntaxhighlight lang="sql"> select aid,sell_num from fmb_new_activity where aid in(oldid,newid); /*查询新老活动销量*/ update fmb_new_activity set sell_num=sell_num+XXX where aid=newid limit 1;/*XXX改为旧活动查询出来的销量值*/ </syntaxhighlight>
批量更新结算价
<syntaxhighlight lang="sql">
update fmb_order_goods fog,fmb_tickets ft set fog.settle_price=ft.settle_price where ft.ticket_group_id=6799 and ft.ticket_id=fog.goods_id
</syntaxhighlight>
统计万圣节或圣诞活动数据
<syntaxhighlight lang="sql"> /*需要替换正式的活动id和票种id*/ /*查询票务及票种信息*/ SELECT * from fmb_new_activity where aid=181745 SELECT * from fmb_group_tickets where aid=181745 SELECT * from fmb_tickets where ticket_group_id=27355 order by tname /*查询用户购买信息*/ SELECT oi.mobile, oi.receiver, og.goods_id AS usetime, og.goods_number, og.goods_id, group_concat( e.exchange_code SEPARATOR ';' ) FROM fmb_order_info oi, fmb_order_goods og, fmb_exchange_codes e WHERE oi.order_sn = og.order_sn AND og.order_sn = e.order_sn AND oi.order_status = 5 AND og.aid = 181745 AND og.goods_id IN ( 414053, 414055, 414057, 414059 ) GROUP BY oi.`order_sn` ORDER BY og.goods_id ASC /***查票种属性**/ SELECT v.* FROM fmb_goods_attr_val v LEFT JOIN fmb_goods_type_attr a ON v.attr_id = a.goods_type_attr_id WHERE v.goods_id IN ( SELECT ticket_id FROM fmb_tickets WHERE ticket_group_id = 27355 ) ORDER BY v.goods_id ASC, a.sort_order DESC /***总库存**/ SELECT sum(og.goods_number) FROM fmb_order_info oi, fmb_order_goods og WHERE oi.order_sn = og.order_sn AND oi.order_status = 5 AND og.aid = 181745 AND og.goods_id IN ( 413493, 413511, 413421, 413439 ) ORDER BY og.goods_id ASC
</syntaxhighlight>
