From surenkid, 1 Year ago, written in MySQL.
Embed
  1. -- 总订单
  2. CREATE TEMPORARY TABLE IF NOT EXISTS sorder
  3. SELECT id,memberid,wechatorderid,mobile,`name`,money,`time`,debug_status FROM `smwx_order`
  4. WHERE `time`>'2018-11-21 11:59:59' AND `status` = 1 AND `name` like 'vinistyle十周年感恩礼盒%';
  5.  
  6. -- 长街支付订单
  7. CREATE TEMPORARY TABLE IF NOT EXISTS schanpay
  8. SELECT orderid,wechatorderid,trade_amount,chanpayorderid,create_time,`status` FROM `smwx_chanpay_order`
  9. WHERE `create_time`>'2018-11-21 11:59:59';
  10.  
  11. -- 筛选0.01
  12. SELECT DISTINCT mobile FROM sorder
  13. WHERE sorder.debug_status = 1 AND `name` = 'vinistyle十周年感恩礼盒';
  14.  
  15. -- 筛选正常
  16. CREATE TEMPORARY TABLE IF NOT EXISTS s1yes
  17. SELECT DISTINCT mobile FROM sorder
  18. WHERE sorder.debug_status = 0 AND `name` = 'vinistyle十周年感恩礼盒';
  19.  
  20. -- 筛选补单
  21. CREATE TEMPORARY TABLE IF NOT EXISTS s2yes
  22. SELECT DISTINCT mobile FROM sorder
  23. WHERE `name` = 'vinistyle十周年感恩礼盒补价';
  24.  
  25. -- 筛选0.01,没有正常秒杀,没有补单
  26. SELECT * FROM s1no
  27. WHERE mobile not in (select * from s1yes) AND mobile not in (select * from s2yes)