按UserID和Dept计算培训预算剩余的子表单SQL查询问题

首先先明确你的两张表结构，方便后续分析：

Users表（培训预算信息） #

ID | UserID | FName | SName | Dept | Budget
1  | 1      | John  | Smith | CS   | 1000
2  | 2      | Ian   | Caine | CS   | 2500
3  | 3      | Jane  | Kelly | ED   | 1000
4  | 1      | John  | Smith | EQ   | 1000
5  | 2      | Ian   | Caine | EQ   | 2500
6  | 3      | Jane  | Kelly | CS   | 1000

Courses表（已修课程记录） #

ID | UserID | Course | Date   | Dept | Cost
1  | 1      | CS01   | 1/4/18 | CS   | 100
2  | 2      | CS01   | 1/4/18 | CS   | 100
3  | 1      | CS02   | 10/4/18| CS   | 75
4  | 2      | CS02   | 10/4/18| CS   | 75
5  | 1      | CS01   | 1/4/18 | EQ   | 100

你当前的SQL问题很明确：子查询没有针对UserID和Dept做分组过滤，所以计算的是全局所有课程的累计，而不是同一个用户+同一个部门的课程累计。下面给你两种可行的修正方案：

方案1：关联子查询实现分组累计（兼容多数数据库，包括Access） #

这个写法不需要依赖高级窗口函数，适合大多数数据库环境：

SELECT 
    c.UserID,
    c.Course,
    c.Date,
    c.Cost,
    u.Budget - (
        SELECT SUM(c2.Cost) 
        FROM tbl_Courses c2 
        WHERE c2.UserID = c.UserID 
          AND c2.Dept = c.Dept 
          AND c2.ID <= c.ID
    ) AS [Balance Remaining]
FROM tbl_Courses c
INNER JOIN Users u 
    ON c.UserID = u.UserID 
    AND c.Dept = u.Dept
ORDER BY c.UserID, c.Dept, c.ID;

核心改进点： #

1. 在子查询中添加了c2.UserID = c.UserID和c2.Dept = c.Dept，确保累计范围限制在当前用户的同一部门课程内；
2. 关联Users表获取该用户对应部门的初始预算，这样才能计算出剩余金额；
3. 按UserID、Dept、ID排序，保证累计顺序和课程记录的先后一致。

方案2：窗口函数实现（高效简洁，适合支持窗口函数的数据库） #

如果你的数据库支持窗口函数（比如SQL Server、PostgreSQL、MySQL 8.0+等），可以用更高效的写法：

SELECT 
    c.UserID,
    c.Course,
    c.Date,
    c.Cost,
    u.Budget - SUM(c.Cost) OVER (
        PARTITION BY c.UserID, c.Dept 
        ORDER BY c.ID 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS [Balance Remaining]
FROM tbl_Courses c
INNER JOIN Users u 
    ON c.UserID = u.UserID 
    AND c.Dept = u.Dept
ORDER BY c.UserID, c.Dept, c.ID;

核心说明： #

• PARTITION BY c.UserID, c.Dept 表示按用户和部门分组计算累计；
• ORDER BY c.ID 保证累计顺序是按课程记录的ID先后；
• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 指定累计范围是从分组的第一条记录到当前记录。

子表单与主表单的关联设置 #

最后别忘了在子表单的属性里设置链接字段：把主表单的UserID和Dept作为链接字段，这样子表单只会显示当前主表单用户对应部门的课程记录，完全匹配你想要的示例效果。

比如当主表单选中UserID=1, Dept=CS的记录时，子表单会输出：

Course | Date   | Cost | Balance Remaining
CS01   | 1/4/18 | 100  | 900
CS02   |10/4/18 | 75   | 825

内容的提问来源于stack exchange，提问作者Naz