mysql计算超过两个日期

Question

我有一个 sql 查询，可以在开始和结束相遇时计算分配的小时数。

这是我的sqlfiddle

http://sqlfiddle.com/#!2/9f7634/1

SELECT `Nickname`, `taxemployee_id`, `taxemployee`.`id`, `taxemployee`.`Position`, COALESCE((datediff (`end`, `start`) + 1), 0) * `assigned_hours` as assigned_hours, start,end
FROM (`taxemployee`) 
LEFT JOIN `project_staff_assignment` ON `project_staff_assignment`.`taxemployee_id` = `taxemployee`.`id` 
    AND start >= '2014-09-01' AND end <= '2014-09-15'
    AND end <= '2014-09-15' AND start >= '2014-09-01'    
GROUP BY `taxemployee`.`id` 
ORDER BY `Nickname` ASC

查询显示 2014-09-01 到 2014-09-15 之间的所有记录。

如果我有一条记录，起始值为 2014-09-01，结束值为 2014-09-30，我无法在这些日期之间获得任何结果。

这是我的插入记录。

(7, 5, 'Senior2', 12, '7', '2014-09-01', '2014-09-30', 'active')

我需要根据上面的查询返回值。也许我的查询是错误的

Answer 1

让我们从这个开始......

SELECT t.Nickname
     , psa.taxemployee_id
     , t.id
     , t.Position
     , psa.start
     , psa.end
  FROM taxemployee t 
  LEFT 
  JOIN project_staff_assignment psa
    ON psa.taxemployee_id = t.id;

+----------+----------------+----+-----------------+------------+------------+
| Nickname | taxemployee_id | id | Position        | start      | end        |
+----------+----------------+----+-----------------+------------+------------+
| Walter   |           NULL |  1 | Partner         | NULL       | NULL       |
| Eric     |           NULL |  2 | Partner         | NULL       | NULL       |
| Richard  |           NULL |  3 | Partner         | NULL       | NULL       |
| Elaine   |              4 |  4 | Senior Manager  | 2014-08-18 | 2014-08-22 |
| Tan-tan  |           NULL |  5 | Senior Manager  | NULL       | NULL       |
| Alvin    |              6 |  6 | Senior Manager  | 2014-08-01 | 2014-08-15 |
| Alvin    |              6 |  6 | Senior Manager  | 2014-08-18 | 2014-08-22 |
|          |           NULL |  7 | Senior Manager  | NULL       | NULL       |
| Jackie   |           NULL |  8 | Manager 2       | NULL       | NULL       |
| Ayee     |              9 |  9 | Manager 2       | 2014-08-01 | 2014-08-01 |
| Dan      |             10 | 10 | Manager 2       | 2014-08-11 | 2014-08-15 |
| Acel     |           NULL | 11 | Manager 1       | NULL       | NULL       |
| Nim      |             12 | 12 | Manager 1       | 2014-09-01 | 2014-09-30 |
| Joem     |           NULL | 13 | Asst. Manager 2 | NULL       | NULL       |
| Jen      |           NULL | 14 | Asst. Manager 2 | NULL       | NULL       |
| Joanne   |             15 | 15 | Asst. Manager 2 | 2014-08-25 | 2014-08-29 |
| Berenice |           NULL | 16 | Asst. Manager 1 | NULL       | NULL       |
+----------+----------------+----+-----------------+------------+------------+

现在，给定这个中间结果，期望的结果会是什么样子？