Mysql场景-即使没有条目也获取所有任务?

发布于 2024-08-18 12:14:20 字数 1826 浏览 3 评论 0原文

我有三个表

  1. Tasks ,其中包含 Taskid、Taskname
  2. TaskAllocations ,其中包含 Taskid、EmpNum
  3. TaskEntries 列code> 与列 TaskId、EmpNum、WorkedDate、Hoursspent

现在我想获取特定一周内的所有任务条目。这里我的问题是,即使没有特定任务的 Taskentry,我也应该在查询结果集中至少得到一行包含该 TaskId 和 Hoursspent 为 Null 的任务名称。我一直在尝试通过以下查询来获取此信息。

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 ';

我从这个 SQL 片段中得到的是当且仅当存在特定任务 id 的条目时,那么只有我才能获得该行。但我想要的是为 EmpNum 可用的每个任务至少获取一行。即使我为每个 TaskId 和 WorkedDate 组合获取一行也没有问题。请帮我解决这个问题。其实际目的是构建一个 HTML 二维表,其中每个任务条目都对应日期和任务,如下所示。

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

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


注意:对于特定的任务 ID 和工作日期,只有一项花费的小时数。 我几乎已经构建了前端。即使没有条目,请帮助我获取上述所有任务 ID。我需要使用子查询吗?

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.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

吾家有女初长成 2024-08-25 12:14:20

不要使用内部联接,使用左联接或右联接,具体取决于您想要哪些表中的值。

所以:

SELECT *
FROM tasks t
LEFT JOIN taskentries te
ON t.id = te.id

这与以下语句相同:

SELECT *
FROM tasksentries te
RIGHT JOIN tasks t
ON te.id = t.id

将获得所有任务,即使没有任务条目,

内部联接只会在两个表中都有行时选择行,左联接从左(第一个)表中选择所有行并匹配从另一行(如果没有这样的行,则 null 将是所有列的值)。右连接将执行相反的操作:从右(第二个)表中选择所有行并从左表中匹配。

a LEFT JOIN bb RIGHT JOIN a 相同

don't user an inner join, use a left or right join, depending which values from which tables you want.

so:

SELECT *
FROM tasks t
LEFT JOIN taskentries te
ON t.id = te.id

which is the same statement as:

SELECT *
FROM tasksentries te
RIGHT JOIN tasks t
ON te.id = t.id

will get you all tasks, even if there is no taskentry

an inner join will only select rows when there are rows in both tables, left join selects all rows from the left (first) table and matching from the other row (if there is no such row, null will be the value of all columns). right join will do the oposite: select all rows from right (second) table and matching from left.

a LEFT JOIN b is the same as b RIGHT JOIN a

芯好空 2024-08-25 12:14:20

经过对不同选项的严格测试后,我提出了以下解决方案,它将为我提供所需的结果。

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 ;

我在问题中的第一个查询不起作用,因为我在执行左外连接时在右表的列上放置了条件。感谢大家的支持。

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.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文