sql - 复杂SQL语句可行性

Question

我目前正在为一所学校开发一个数据库系统，该系统将与我们的预订系统配合使用，以便学生可以预订他们的类(class)，我们可以更好地跟踪他们的事件。现在我面临一个复杂的问题，我想做的是检查哪些学生契约(Contract)有效，我可以通过使用以下功能来做到这一点，但我想看看是否有更简单的方法来正确地做到这一点(除了存储我可以在系统中计算的数据。)

如果契约(Contract)有效，那么学生还没有用完他们购买的所有小时数，购买的小时数是他们参加的教学课时长的总和加上他们错过但预订的每节课时长的总和(在某个数字之后，例如 3 或 5)。我可以使用以下查询来完成，但我觉得必须有更简单的方法:

SELECT
    level.name
FROM
    (
        SELECT
            contract.level_package_id,
            contract_class_hours.hours_purchased,
            (
                SELECT
                    isnull(sum(DATEPART(hh, class.end_date - class.start_date)), 0)
                FROM
                    booking
                    JOIN class ON class.id = booking.class_id
                WHERE
                    booking.booking_state_id = 3
                    AND booking.contract_id = contract.id
            ) AS time_attended,
            (
                SELECT 
                    isnull(sum(absent_class_lengths.length), 0)
                FROM
                    (
                        SELECT
                            DATEPART(hh, class.end_date - class.start_date) AS length,
                            row_number() OVER (ORDER BY class.start_date) AS rn
                        FROM
                            booking
                            JOIN class ON class.id = booking.class_id
                        WHERE
                            student_id = 5
                            AND booking_state_id = 4
                            AND booking.contract_id = contract.id
                    ) absent_class_lengths
                WHERE 
                    rn > contract_class_hours.absences_allowed
            ) as time_absent
        FROM
            contract
            JOIN contract_class_hours ON contract_class_hours.contract_id = contract.id
    ) test
    JOIN level_package_level ON level_package_level.level_package_id = test.level_package_id
    JOIN level ON level.id = level_package_level.level_id
WHERE
    test.time_absent + test.time_attended < test.hours_purchased
    AND level.study_type_id = 2

• booking.state_id = 3 表示学生已上课
• booking.state_id = 4 表示学生缺席
• level.study_type_id = 2 只是一个类(class)主题

表格包含这样的数据列(忽略 level_id，它只是我需要返回的值):

CLASS
    id - int
    end_date - datetime
    start_date - datetime

BOOKING
    id - int
    class_id - int
    student_id - int
    booking_state_id - smallint

BOOKING_STATE
    id - int
    state - varchar(20) [absent, attended]

CONTRACT
    id - int
    student_id - int
    level_id - int
    hours_purchased - smallint
    absenses_allowed - smallint

STUDENT
    id - int

我意识到这可能是一个复杂的问题，但我只是想知道这是否真的是正确的做事方式，或者我是否应该只在契约(Contract)表中保存一些字段出勤小时数并假设它始终准确。

Answer 1

看来你可以做一些事情来改善这种情况

1. 使用一些代码创建 View
2. 使用上面的响应创建一个存储过程，并制作硬编码的ids变量其中 class.student_id = 5 AND booking_state_id = 4

我猜测这些并不总是相同的，与 AD Hoc sql 语句相比，拥有存储过程将允许缓存计划。这将允许您发送各种 ID 以获得您想要的结果，并返回一个数据集，该数据集可用于 Web 应用程序，只需转换为 Exce 或 Word 表格即可进行报告。