去重 四天之内 与 普通查询(不去重,当天)的查询结果对比

1.去重查询  439通道  1月30日  送达成功   的数据

SELECT
response.tunnel_id AS tunnelId,
record.add_date AS addDate,
t.type AS ttype,
t.sequence_number AS tsequense,
record.mobile,
record.content,
record.create_date
FROM ty_sms_send_record AS record  
   LEFT JOIN ty_sms_send_response AS response  on response.add_date IN ('2018-01-30') 
   and  record.id = response.send_record_id 
   LEFT JOIN ty_sms_send_report AS report ON report.add_date IN 
       ('2018-01-30','2018-01-31','2018-02-01','2018-02-02') 
      and  report.msg_id = response.msg_id 
      and report.mobile=record.mobile
    LEFT JOIN ty_tunnel AS t ON response.tunnel_id = t.id
WHERE
record.add_date IN ('2018-01-30') AND
report.arrived_status = 1 AND
t.type IN (4) AND
t.sequence_number = 39 

GROUP BY response.msg_id
ORDER BY
record.create_date

2.当前不带去重的查询(加入了时分秒),应答时间是当天返回 时间范围是'2018-04-08 09:35:24' and '2018-04-08 09:35:28'

SELECT
customer.serial_number AS `customer.serialNumber`,
customer.special_service_number AS `customer.specialServiceNumber`,
customer.company_name AS `customer.companyName`,
agent.`name` AS `customer.agent.name`,
a.mobile,
a.content,
a.resend_id,
a.id,
a.refuse_send_message,
a1.send_status,
a1.send_result_message,
a2.arrived_status,
a2.arrived_time,
a2.arrived_result_message,
tunnel.type AS `tunnel.type`,
tunnel.sequence_number AS `tunnel.sequenceNumber`,
a.create_date,
a.submit_log_id AS `messageSubmitLog.id`
FROM ty_sms_send_record a LEFT JOIN ty_customer customer ON customer.id 
= a.customer_id LEFT JOIN ty_agent agent on agent.id = customer.agent_id LEFT JOIN ty_sms_send_response 
a1 ON a.id = a1.send_record_id AND a1.add_date IN ( '2018-04-08' ) AND a1.create_date BETWEEN 
'2018-04-08 09:35:24' and '2018-04-08 09:35:28' LEFT JOIN ty_tunnel tunnel ON tunnel.id = a1.tunnel_id 
LEFT JOIN ty_sms_send_report a2 ON a1.msg_id = a2.msg_id AND a2.mobile = a.mobile AND a2.add_date 
IN ( '2018-04-08' , '2018-04-09' , '2018-04-10' ) AND a2.create_date BETWEEN '2018-04-08 09:35:24' 
and '2018-04-11 09:35:28'
WHERE a.del_flag = '0' AND a.add_date IN ( '2018-04-08' ) AND a.create_date 
BETWEEN '2018-04-08 09:35:24' and '2018-04-08 09:35:28'
order by a.create_date desc


  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值