查看数据查询文档的源代码

'''主要用来记录日常开发过程当中，查询数据统计用到的sql语句，这样针对下次同样需求的时候能够快速获取相关数据'''

== 操作流程 ==

#开发同事处理相关的统计结果
#发送相应的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>



=== 按验证码拆分退货表 ===

<pre>
原始：------------拆分后：
 ["1","a,b,c",9]--["1","a",3]
 ["2","",5]-------["1","b",3]
------------------["1","c",3]
------------------["2","",5]
</pre> <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>

=== 票务分类 ===

<pre>
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
</pre>



=== 无结算价问题订单 ===

<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>