不使用 WHERE 语句的 INT 比较

我正在尝试编写一条 MySQL 语句来返回这些结果：

## Name | Day 0 | Day 1 | Day 2 | Day 3 | Day 4 | Day 5 |
##Jeff  |   0   |    3  |     1 |     2 |    1  |   1   |
##Larry |   1   |    1  |     4 |     4 |    1  |   0   |

基于每个员工每天执行的任务数量。

我的数据库表如下：

Employee

id (INT), number（VARCHAR），name（VARCHAR），dateStarted（VARCHAR），

Project

id (INT), number（VARCHAR），dateEnded（约会时间）

现在我正在使用这个声明：

SELECT 
a.name AS "Name",
count(abs(datediff(STR_TO_DATE(a.dateStarted, '%Y-%m-%d %H:%i:%s'), b.dateEnded))) AS "Day 0",
count(abs(datediff(STR_TO_DATE(a.dateStarted, '%Y-%m-%d %H:%i:%s'), b.dateEnded))) AS "Day 1",
count(abs(datediff(STR_TO_DATE(a.dateStarted, '%Y-%m-%d %H:%i:%s'), b.dateEnded))) AS "Day 2",
count(abs(datediff(STR_TO_DATE(a.dateStarted, '%Y-%m-%d %H:%i:%s'), b.dateEnded))) AS "Day 3",
count(abs(datediff(STR_TO_DATE(a.dateStarted, '%Y-%m-%d %H:%i:%s'), b.dateEnded))) AS "Day 4",
count(abs(datediff(STR_TO_DATE(a.dateStarted, '%Y-%m-%d %H:%i:%s'), b.dateEnded))) AS "Day 5"
FROM employee a, project b
WHERE b.number=a.number 
AND "Day 0" = 0
AND "Day 1" = 1
AND "Day 2" = 2
AND "Day 3" = 3
AND "Day 4" = 4
AND "Day 5" >= 5

电流输出

上述语句有效，但由于某种原因，它没有提供上述要求中提到的所需结果。关于如何修复/更改它有什么想法吗？

edit

如果我取出：

AND "Day 0" = 0
AND "Day 1" = 1
AND "Day 2" = 2
AND "Day 3" = 3
AND "Day 4" = 4
AND "Day 5" >= 5

然后打印出：

## Name | Day 0 | Day 1 | Day 2 | Day 3 | Day 4 | Day 5 |
##Jeff  |   9   |    9  |     9 |     9 |    9  |   9   |

可以通过执行以下 join 语句或 where.. 来改进以前的解决方案。

确保项目（编号，日期结束）有一个索引（唯一？） 您还可以尝试添加员工索引（编号，开始日期）

NULL 值通常不属于任何索引（因为它们为空），您可以尝试将该字段的默认值更新为“0000-00-00”，这样它将被索引并可能提高速度。

尝试这个：

SELECT a.name AS "Name", 
       SUM(noOfDays = 0) AS "Day 0", SUM(noOfDays = 1) AS "Day 1", 
       SUM(noOfDays = 2) AS "Day 2", SUM(noOfDays = 3) AS "Day 3", 
       SUM(noOfDays = 4) AS "Day 4", SUM(noOfDays >= 5) AS "Day 5" 
FROM (SELECT a.number, a.name, DATEDIFF(DATE(b.dateEnded), DATE(a.dateStarted)) noOfDays
      FROM employee a INNER JOIN project b ON (b.number = a.number and b.dateEnded>a.dateStarted)
    ) AS a
GROUP BY a.name

检查SQL FIDDLE 演示 http://sqlfiddle.com/#!2/db498/10

|    NAME | DAY 0 | DAY 1 | DAY 2 | DAY 3 | DAY 4 | DAY 5 |
|---------|-------|-------|-------|-------|-------|-------|
|  ##Jeff |     0 |     3 |     1 |     2 |     1 |     1 |
| ##Larry |     1 |     1 |     4 |     4 |     1 |     0 |