MySQL Query查找两个日期范围之间的记录 [英] MySQL Query to find records between two date range

问题描述

我在使用PHP和MySQL的Web应用程序中工作。



我有两张桌子



1。员工

 employee_id email_id 
 3 [e-mail] 
 4 [e-mail] 
 5 [e-mail] 
 6 [e-mail] 
 7 [e-mail] 

2。教练

 coaching_id emp_id start_date end_date 
 1 4 2014-05-01 2014-10-02 
 2 5 2014-12-18 2015-01-22 

我想查询一下，找出未在指定日期范围内注册任何辅导课程的员工例如开始日期2015-01-22和结束日期2015-03-12。



现在我正在使用这样的查询，

  SELECT  employee_id  FROM  employee  WHERE  sup_id =  3   AND  employee_id  NOT   IN （ SELECT  emp_id  FROM （辅导） WHERE （start_date  NOT   BETWEEN    2014-12-26  AND    2015-01-30） AND （end_date  NOT   BETWEEN    2014-12-26  AND    2015-01-30））

在哪里我的查询中的问题



谢谢

解决方案

试试这个：

< pre lang =SQL> SELECT e.employee_id
FROM employee AS e INNER JOIN coaching AS c ON e.employee_id = c.emp_id
WHERE e.sup_id = 3 AND c.start_date NOT BETWEEN 2014-12-26 AND 2015-01-30 AND c.end_date NOT BETWEEN 2014-12-26 AND 2015-01-30

我建议阅读这： SQL连接的可视化表示 [ ^ ]

引用：

我想要一个查询，找出未在指定日期范围（例如开始日期2015-01-）之间注册参加任何辅导计划的员工22和结束日期2015-03-12。

包括尚未注册任何计划的员工以及未在指定期限内注册的员工。您可以使用LEFT加入

  SELECT  e.employee_id 
  FROM 员工e 
  LEFT   JOIN 指导c 
  ON  e.employee_id = c.emp_id 
  WHERE  c.emp_id  IS   NULL  或 
  not （c.start_date  BETWEEN  '  2014-12-26'  AND  '  2015-01-30' 
 或 c.end_date  BETWEEN  '  2014-12-26'  AND  '  2015-01-30'）

DEMO [ ^ ]

I am working in a web application using PHP and MySQL.

I have two tables

1. Employee

employee_id    email_id
    3          [e-mail]
    4          [e-mail]
    5          [e-mail]
    6          [e-mail]
    7          [e-mail]

2. Coaching

coaching_id   emp_id  start_date  end_date
  1               4     2014-05-01  2014-10-02
  2               5     2014-12-18  2015-01-22

I want a query to find out the employees who are not enrolled for any coaching program between a specified date range such as Start Date "2015-01-22" and End Date "2015-03-12".

Now I am using query like this,

SELECT employee_id FROM employee WHERE sup_id = 3 AND employee_id NOT IN (SELECT emp_id FROM (coaching) WHERE (start_date NOT BETWEEN "2014-12-26" AND "2015-01-30") AND (end_date NOT BETWEEN "2014-12-26" AND "2015-01-30"))

Where is the issue in my query

Thanks

解决方案

Try this:

SELECT e.employee_id
FROM employee AS e INNER JOIN coaching AS c ON e.employee_id = c.emp_id
WHERE e.sup_id = 3 AND c.start_date NOT BETWEEN "2014-12-26" AND "2015-01-30" AND c.end_date NOT BETWEEN "2014-12-26" AND "2015-01-30"

I'd suggest to read this: Visual Representation of SQL Joins[^]

Quote:

I want a query to find out the employees who are not enrolled for any coaching program between a specified date range such as Start Date "2015-01-22" and End Date "2015-03-12".

that include employees who haven't enrolled any program plus employees not enrolled between specified period. You can do this using LEFT Join

SELECT e.employee_id
FROM Employee e
LEFT JOIN Coaching c
ON e.employee_id = c.emp_id
WHERE c.emp_id IS NULL or  
 not (c.start_date  BETWEEN '2014-12-26' AND '2015-01-30' 
        or c.end_date  BETWEEN '2014-12-26' AND '2015-01-30')

DEMO[^]

这篇关于MySQL Query查找两个日期范围之间的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！