mysql之SQL查询以查找剩余叶子数量最多的讲师

dflying 阅读:29 2024-02-27 23:08:18 评论:0

我正在尝试找出显示剩余叶子数最多的讲师的查询。以下是表名和字段:

Lecturer (lect_id, name, join_date, quit_date, no_of_leaves) 
Leave (leave_id, lect_id, used_leave, start_date, end_date) 

表格“讲师”

lect_id  name      join_date    quit_date   no_of_leaves 
1        Andy      01/12/07     NULL        20 
2        Bob       15/02/14     31/04/16    20 
3        Carrot    05/07/15     NULL        15 

表“离开”

leave_id    lect_id used_leave  start_date  end_date 
101         1       3           04/01/09    06/01/09 
102         1       4           12/02/12    15/02/12 
103         3       6           23/02/16    28/02/16 

正确查询,结果应显示姓名、讲师 ID 和剩余叶子数:

name    lect_id   NoOfLeavesLeft 
Andy    1         13 (Resulted from 20 - 3 - 4) 
Bob     2         20 (Remains the same because no leave taken) 
Carrot  3         9 (Resulted from 15 - 6) 

我试过这个查询:

SELECT lect_id, name, SUM(no_of_leaves) NoOfLeavesLeft 
FROM ( 
    (SELECT name, lect_id, no_of_leaves FROM Lecturer lc) 
    UNION ALL 
    (SELECT Null as col1, lect_id, - le.used_leave FROM Leave le) 
    ) lect 
GROUP BY lect_id 
ORDER BY NoOfLeavesLeft DESC 

我的问题:

我能够从这个查询中得到正确的结果。 除了使用 UNION ALL 并将 NULL 作为列传递之外,还有其他方法可以查询吗?

请您参考如下方法:

您可以左连接并使用聚合:

select  
    l.lect_id,  
    l.name,  
    l.no_of_leaves - coalsece(sum(v.used_leave), 0) no_of_leaves_left 
from lecturer l 
left join leave v on v.lect_id = l.lect_id 
group by l.lect_id, l.name 

另一种避免使用聚合并可能比上述查询执行得更好的选项(尤其是在 leave(lect_id, used_leave) 上有索引)是使用内联子查询,如下所示:

select  
    l.lect_id,  
    l.name,  
    l.no_of_leaves  
        - ( 
            select coalesce(sum(v.used_leave), 0) 
            from leave v 
            where v.lect_id = l.lect_id 
        )  
    no_of_leaves_left 
from lecturer l 


标签:mysql
声明

1.本站遵循行业规范,任何转载的稿件都会明确标注作者和来源;2.本站的原创文章,请转载时务必注明文章作者和来源,不尊重原创的行为我们将追究责任;3.作者投稿可能会经我们编辑修改或补充。

关注我们

一个IT知识分享的公众号