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

Question

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

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 作为列传递之外，还有其他方法可以查询吗？

Answer 1

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

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