没有 WHERE 语句的 INT 比较
我正在尝试编写一个 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 |
相关文章