0
SELECT * FROM `order` where pay_status = 1 AND paytime BETWEEN '2019-06-11 00:00:01' AND '2019-06-11 23:59:59' AND order_type = 1 AND order_id NOT IN(SELECT order_id FROM `order` where pay_status = 1 AND paytime BETWEEN '2019-06-11 00:00:01' AND '2019-06-11 23:59:59' AND order_type = 1 GROUP BY userid)

这个sql 查询 一天 有没有买重复的 前几天都很好用 今天有反馈说漏了一个 我查了查 确实漏了

SELECT * FROM `order` where pay_status = 1 AND paytime BETWEEN '2019-06-11 00:00:01' AND '2019-06-11 23:59:59' AND order_type = 1 AND order_id

SELECT order_id FROM `order` where pay_status = 1 AND paytime BETWEEN '2019-06-11 00:00:01' AND '2019-06-11 23:59:59' AND order_type = 1 GROUP BY userid

的结果集差了1个,时间是在中午 条件也都符合 但是合起来的 那个sql 就查不出来
头一次遇见这种事,求大神 指教 或者说说可能是哪方面的问题

symbol 3
2019-06-14 提问
1 个回答
0

已采纳

1.建议你explain SQL,然后看一下show warnings,估计是优化器把你的SQL改成not exists了;
2.我看你的逻辑,如果每个userid下多于2个order_id,就找出来?以userid和order_id分组,判断分组后是否唯一不行么?group by userid,order_id having count(*)>1;

撰写答案

推广链接