我正在处理一个需要以下查询的项目,但我无法完成。
数据库:evenstdb
--------------------------------------
| Event_id | startDate | endDate |
--------------------------------------
|82637 |01-02-2020 | 02-03-2020 |
--------------------------------------
|66363 |10-03-2020 | 31-11-2020 |
--------------------------------------
我有一个页面,我应该根据选择的日期范围列出事件。一切都很好,但问题是,有时我会将同一个事件列出两次,因为我同时根据startDate
和endDate
列出事件。有时开始日期和结束日期都在选定的日期范围内。如何只显示一次事件?
我的代码:
// $date1 and $date2 are date inputs from the form
$query = $this->db->where('startDate >=', $date1);
$query = $this->db->where('startDate <=', $date2);
$query = $this->db->get('evenstdb');
$results1 = $query->result();
$query = $this->db->where('endDate >=', $date1);
$query = $this->db->where('endDate <=', $date2);
$query = $this->db->get('evenstdb');
$results2 = $query->result();
$data["eventsList"] = array_merge($results1,$results2);
如果每条记录应同时匹配两个日期范围,则可以尝试通过一个具有所有4个条件的查询来获取所需记录:
$this->db->where('startDate >=', $date1)
->where('startDate <=', $date2)
->where('endDate >=', $date1)
->where('endDate <=', $date2);
$result = $this->db->get('evenstdb')->result();
如果每条记录至少匹配一个日期范围,您也可以尝试通过一个查询获得所需的记录,但将条件分组为与OR
连接的两个部分(如果这两个组中的任何一个返回true
,则将返回记录(:
$this->db->group_start()
->where('startDate >=', $date1)
->where('startDate <=', $date2)
->or_group_start()
->where('endDate >=', $date1)
->where('endDate <=', $date2)
->group_end()
->group_end();
$result = $this->db->get('evenstdb')->result();
我最近需要一个查询来查找MySQL中重叠的日期范围。此解决方案的性能比上面的日期范围解决方案好得多,而且工作非常完美。
https://stackoverflow.com/a/62761904/11822079
你可以做类似的事情:
AND DATEDIFF(LEAST($date2, endDate),GREATEST($date1, startDate)) > 0;
链接查询示例:
SELECT o.orderStart, o.orderEnd, s.startDate, s.endDate
, GREATEST(LEAST(orderEnd, endDate) - GREATEST(orderStart, startDate), 0)>0 as overlaps
, DATEDIFF(LEAST(orderEnd, endDate), GREATEST(orderStart, startDate)) as overlap_length
FROM orders o
JOIN dates s USING (customerId)
WHERE 1
AND DATEDIFF(LEAST(orderEnd, endDate),GREATEST(orderStart, startDate)) > 0;