From surenkid, 10 Months 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. -- 所有重复2次以上的手机号
  12. CREATE TEMPORARY TABLE IF NOT EXISTS rmobile
  13. SELECT mobile FROM sorder GROUP BY mobile HAVING count(mobile)>1;
  14.  
  15. -- 所有重复2次以上的手机号
  16. SELECT * FROM sorder
  17. LEFT JOIN schanpay ON sorder.wechatorderid = schanpay.wechatorderid
  18. WHERE sorder.mobile in (SELECT mobile FROM rmobile)
  19. ORDER BY sorder.mobile;