我正在尝试编写一条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 |
基于每个员工每天执行多少任务.
我的数据库表如下:
雇员
id(INT),数字(VARCHAR),名称(VARCHAR),dateStarted(VARCHAR),
项目
id(INT),数字(VARCHAR),dateEnded(DATETIME)
现在,我正在使用以下语句:
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
电流输出
上面的语句有效,但是由于某种原因,它不能提供上述要求中提到的期望结果.关于如何修复/更改它的任何想法?
编辑
如果我取出:
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
emp.name AS '## Name',
(SELECT COUNT(*)
FROM project p JOIN employee e ON p.number = e.number
WHERE e.name = emp.name
AND datediff(str_to_date(p.dateEnded, '%Y-%m-%d'), e.dateStarted) = 0
) AS 'Day 0'
,(SELECT COUNT(*)
FROM project p JOIN employee e ON p.number = e.number
WHERE e.name = emp.name
AND datediff(str_to_date(p.dateEnded, '%Y-%m-%d'), e.dateStarted) = 1
) AS 'Day 1'
,(SELECT COUNT(*)
FROM project p JOIN employee e ON p.number = e.number
WHERE e.name = emp.name
AND datediff(str_to_date(p.dateEnded, '%Y-%m-%d'), e.dateStarted) = 2
) AS 'Day 2'
,(SELECT COUNT(*)
FROM project p JOIN employee e ON p.number = e.number
WHERE e.name = emp.name
AND datediff(str_to_date(p.dateEnded, '%Y-%m-%d'), e.dateStarted) = 3
) AS 'Day 3'
,(SELECT COUNT(*)
FROM project p JOIN employee e ON p.number = e.number
WHERE e.name = emp.name
AND datediff(str_to_date(p.dateEnded, '%Y-%m-%d'), e.dateStarted) = 4
) AS 'Day 4'
,(SELECT COUNT(*)
FROM project p JOIN employee e ON p.number = e.number
WHERE e.name = emp.name
AND datediff(str_to_date(p.dateEnded, '%Y-%m-%d'), e.dateStarted) >= 5
) AS 'Day 5'
FROM employee emp
GROUP BY emp.name
请参阅SQL Fiddle Demo(-已根据提供的信息对您的数据进行了一些假设).