没有WHERE语句的INT比较 [英] INT comparison without WHERE statement

问题描述

  ## Name |第0天|第1天|第2天|第3天|第4天|第5天| 
 ## Jeff | 0 | 3 | 1 | 2 | 1 | 1 | 
 ## Larry | 1 | 1 | 4 | 4 | 1 | 0 | 
  

根据每位员工每天执行多少任务。

我的数据库表格如下：

员工

id （INT）， number （VARCHAR）， name （VARCHAR）， dateStarted （VARCHAR），

项目

$ b $ id （INT）， number （VARCHAR）， dateEnded （DATETIME）

现在我使用这个语句：

  SELECT 
 a.name ASName，
 count（abs（datediff（STR_TO_DATE（a.dateStarted，'％Y-％m-％d ％H：％i：％s'），b.dateEnded）））ASDay 0，
 count（abs（datediff（STR_TO_DATE（a.dateStarted，'％Y-％m-％d％H ：％i：％s'），b.dateEnded）））作为第1天，
计数（abs（datediff（STR_TO_DATE（a.dateStarted，'％Y-％m-％d％H：％） i：％s'），b.dateEnded）））ASDay 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）））ASDay 4，
 count（abs（datediff（STR_TO_DATE（a.dateStarted，'％Y-％m-％d％H：％i：％s'），b.dateEnded）））因为第5天 
 FROM员工a，项目b 
其中b.number = a.number 
和第0天= 0 
和第1天= 1 
 AND第2天= 2 
和第3天= 3 
和第4天= 4 
和第5天> 5 
  

当前输出 $ b

以上声明有效，但由于某些原因，它不提供上述要求中提到的所需结果。关于如何修复/更改它的任何想法？
$ b edit

如果我拿出：

 和第0天= 0 
并且第1天= 1 
 ANDDay 2= 2 
 ANDDay 3= 3 
 ANDDay 4= 4 
 ANDDay 5> = 5 
  
 
 
然后打印出来：
 
 
  ## Name |第0天|第1天|第2天|第3天|第4天|第5天| 
 ## Jeff | 9 | 9 | 9 | 9 | 9 | 9 | 
  
 
 
解决方案
试试这个：
 
 
  SELECT a.name ASName，
 SUM（noOfDays = 0）ASDay 0，SUM（noOfDays = 1）ASDay 1，
 SUM（noOfDays = 4）作为第2天，SUM（noOfDays = 3）作为第3天，
 SUM（noOfDays = 4） > = 5）ASDay 5，
 COUNT（1）ASTotal 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 |第0天|第1天|第2天|第3天|第4天|第5天|总日期| 
 | --------- | ------- | ------- | ------- | ------- | --- ---- | ------- | ------------ | 
 | ##杰夫| 0 | 3 | 1 | 2 | 1 | 1 | 8 | 
 | ##拉里| 1 | 1 | 4 | 4 | 1 | 0 | 11 | 
  
 
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.


My database tables are as follows:

Employee

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

Project

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
Current Output



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?

edit

If I take out:
AND "Day 0" = 0
AND "Day 1" = 1
AND "Day 2" = 2
AND "Day 3" = 3
AND "Day 4" = 4
AND "Day 5" >= 5
Then it prints out:
## Name | Day 0 | Day 1 | Day 2 | Day 3 | Day 4 | Day 5 |
##Jeff  |   9   |    9  |     9 |     9 |    9  |   9   |

解决方案
Try this: 
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;
Check the 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 |


                        
这篇关于没有WHERE语句的INT比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！