去重四天之内与普通查询（不去重，当天）的查询结果对比

最新推荐文章于 2024-08-22 09:35:23 发布

圈圈圈小明

最新推荐文章于 2024-08-22 09:35:23 发布

阅读量524

点赞数

本文链接：https://blog.csdn.net/wenxi2367/article/details/79850963

版权

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