Mysql方案 - 即使没有条目，也可以获取所有任务？ [英] Mysql scenario - Get all tasks even if there is no entry?

问题描述

我有三个表格

1. 任务列 Taskid，Taskname


2. TaskAllocations with columns Taskid，EmpNum TaskEntries with columns TaskId，EmpNum，WorkedDate，Hoursspent

现在我想在特定的一周中获取所有任务条目。在这里，我的问题是即使没有Taskentry的特定任务，我应该得到至少一行TaskId，Taskname的Querys结果集中的小时数为Null。

  SELECT A.TaskId，
 B.TaskName，
 SUM（C.HoursSpent）为TotalHours，
 C.WorkedDate，C.Comments 
 FROM TaskAllocations A 
 LEFT OUTER JOIN TaskEntries C 
 ON A.TaskId = C TaskId 
 AND A.EmpNum = C.EmpNum 
 INNER JOIN任务B 
 ON A.TaskId = B.TaskId 
 WHERE A.EmpNum = 123456 
 AND C. WorkedDate 
 IN（'2010-01-17'，'2010-01-18'，'2010-01-19'，
'2010-01-20'，'2010-01-21' ，'2010-01-22'，'2010-01-23'）
 GROUP BY A.TaskId，C.WorkedDate 
 ORDER BY A.TaskId，C.WorkedDate ASC'; 
  

我得到的这个SQL片断是当且仅当有一个特定任务的入口身份证，那么只有我得到了一个行。但是我想要的是为EmpNum提供的每一个任务获得至少一行。即使我为每个TaskId和WorkedDate组合获得一行也没有问题。请帮我解决一下这个。这样做的实际意图是建立一个HTML二维表，每个任务条目与日期和任务相对应，如下所示。

 
 ---- -------------------------------------------------- --- 
 TaskId TaskName星期一星期二星期三星期四星期五星期六
 ------------------------------ --------------------------- 
 18 name1 2 3 4:30 3:30 
 19 name2 
 20 name3 4 2:30 
 22 name4 2:30 
 23 name5 
 24 name6 1:30 6 
 -------------- ------------------------------------------- 
 

这样用户可以每年更新一次。首先，我想到了 group_concat ，但由于性能的原因，我正在使用普通的查询组。

---

注意：对于特定的taskid和workingdate，只有一个小时的输入。
我几乎建立了前端。即使没有条目，请帮助我像上面那样获取所有任务ID。是否需要使用子查询？

解决方案

经过对不同选项的严格测试，我想出了以下解决方案，结果给我。

  SELECT Final.TaskId，
 Final.TaskName，
 Tmp.HoursSpent AS TotalHours ，
 Tmp.WorkedDate 
 FROM（
 SELECT A.TaskId，B.TaskName，A.EmpNum 
 FROM TaskAllocations A 
 INNER JOIN 
任务B 
 ON（A.TaskId = B.TaskId）
 WHERE a.empnum =333
）最终
 LEFT OUTER JOIN（
 SELECT New.TaskId，New。 EmpNum，New.WorkedDate，New.HoursSpent 
 FROM TaskEntries新建
 WHERE New.WorkedDate 
 IN 
（'2010-01-17'，'2010-01-18'， '2010-01-19'，
'2010-01-20'，'2010-01-21'，'2010-01-22'，'2010-01-23'）
或新建.WorkedDat e IS NULL 
 AND New.EmpNum =333
）Tmp 
 ON Tmp.TaskId = Final.TaskId 
 AND Tmp.EmpNum = Final.EmpNum 
 ORDER BY Final.TaskId，Tmp.WorkedDate ASC; 
  

在问题中我的第一个查询不起作用，因为我在右列表的列同时做左外连接。感谢所有的支持。

I have three tables

1. Tasks with columns Taskid, Taskname
2. TaskAllocations with columns Taskid, EmpNum
3. TaskEntries with columns TaskId, EmpNum, WorkedDate, Hoursspent

Now I want to get all the task entries along a particular week. Here my problem is even if there is no Taskentry for a particular task I should get atleast a row with that TaskId, and Taskname with Hoursspent as Null in the query's resultset. I have been trying to get this with the below query.

SELECT A.TaskId, 
       B.TaskName, 
       SUM( C.HoursSpent ) as TotalHours , 
       C.WorkedDate, C.Comments
    FROM TaskAllocations A
    LEFT OUTER JOIN TaskEntries C 
    ON A.TaskId = C.TaskId
    AND A.EmpNum = C.EmpNum
    INNER JOIN Tasks B 
    ON A.TaskId = B.TaskId
    WHERE A.EmpNum =123456
    AND C.WorkedDate
    IN ('2010-01-17','2010-01-18','2010-01-19',
        '2010-01-20','2010-01-21','2010-01-22','2010-01-23' )
    GROUP BY A.TaskId, C.WorkedDate
    ORDER BY A.TaskId,C.WorkedDate ASC ';

What I am getting for this SQL piece is if and only if there is an entry for a particular task id, then only i am getting a row for that. but what I want is to get atleast a row for each and every task that is available to a EmpNum. Even if I get one row for each TaskId and WorkedDate combination no issues. Please help me with this. Actual intention of this is to build a HTML two dimensional table with each task entry against date and task as shown below.

    ---------------------------------------------------------
    TaskId  TaskName   Sun  Mon  Tue  Wed  Thu  Fri  Sat
    ---------------------------------------------------------
    18      name1       2          3        4:30      3:30
    19      name2                                           
    20      name3       4                             2:30
    22      name4       2:30
    23      name5      
    24      name6       1:30               6                
    ---------------------------------------------------------

So that this can be updated by the user for each year week. First I thought of group_concat but because of performance I am using normal group by query.

---

Note: for a particular taskid and workeddate there will be only one entry of hoursspent. I have almost built the frontend. Please help me to get all task ids as above even if there is no entry. Do I need to use subquery.

解决方案

After rigorous testing of different options I came up with the below solution which will give the required results for me.

SELECT Final.TaskId, 
       Final.TaskName, 
       Tmp.HoursSpent AS TotalHours, 
       Tmp.WorkedDate
    FROM (
        SELECT A.TaskId, B.TaskName, A.EmpNum
        FROM TaskAllocations A
                 INNER JOIN 
                 Tasks B 
            ON ( A.TaskId = B.TaskId ) 
        WHERE a.empnum = "333"
        )Final
    LEFT OUTER JOIN (
            SELECT New.TaskId, New.EmpNum, New.WorkedDate, New.HoursSpent
        FROM TaskEntries New
        WHERE New.WorkedDate
        IN 
              ('2010-01-17','2010-01-18','2010-01-19',
              '2010-01-20','2010-01-21','2010-01-22','2010-01-23' )
        OR New.WorkedDate IS NULL 
        AND New.EmpNum = "333"
        )Tmp 
    ON Tmp.TaskId = Final.TaskId
AND Tmp.EmpNum = Final.EmpNum
    ORDER BY Final.TaskId, Tmp.WorkedDate ASC ;

The first query of mine in the question was not working as I was putting a condition on right table's column while doing Left Outer Join. Thanks to all for the support.

这篇关于Mysql方案 - 即使没有条目，也可以获取所有任务？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！