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

Im trying to write a MySQL statement that will bring me back these results:

## 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   |

基于每位员工每天执行的任务数.

Based on how many tasks each employee performed on each day.

我的数据库表如下:

员工

id (INT), number (VARCHAR), name (VARCHAR), dateStarted (VARCHAR),

id (INT), number (VARCHAR), name (VARCHAR), dateStarted (VARCHAR),

项目

id (INT), number (VARCHAR), dateEnded (DATETIME)

id (INT), number (VARCHAR), dateEnded (DATETIME)

现在我正在使用这个语句:

Right now I'm using this statement:

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

电流输出

上述语句有效，但由于某种原因，它不能提供上述要求中提到的预期结果.关于如何修复/更改它的任何想法?

The above statement works but for some reason it does not provide the desired result mentioned in the requirement above. Any ideas on how I can fix/change it?

编辑

如果我拿出来:

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   |

推荐答案

试试这个:

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", 
       COUNT(1) AS "Total Days"
FROM (SELECT a.name, DATEDIFF(DATE(b.dateEnded), DATE(a.dateStarted)) noOfDays
      FROM employee a INNER JOIN project b ON b.number = a.number 
      WHERE b.dateEnded IS NOT NULL
    ) AS A
GROUP BY a.name;

查看SQL FIDDLE DEMO

|    NAME | DAY 0 | DAY 1 | DAY 2 | DAY 3 | DAY 4 | DAY 5 | TOTAL DAYS |
|---------|-------|-------|-------|-------|-------|-------|------------|
|  ##Jeff |     0 |     3 |     1 |     2 |     1 |     1 |          8 |
| ##Larry |     1 |     1 |     4 |     4 |     1 |     0 |         11 |