数据查询文档

来自技术开发小组内部wiki
跳转至： 导航、 搜索

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

目录

操作流程

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

取自“http://wiki.fumubang.net/index.php?title=数据查询文档&oldid=6669”