编写SQL查询：筛选指定日期活跃的学生并获取其当前状态

Got it, let's break down this problem into two clear steps to get exactly what you need: first identify students who were active on June 1, 2015, then pull their current status (regardless of whether it's now active or inactive).

Approach 1: Window Functions (Recommended for Most Databases) #

Using ROW_NUMBER() makes it easy to grab the latest status for each student, then we filter down to only those who were active on the target date.

WITH student_current_status AS (
    SELECT 
        id,
        status AS current_status,
        -- Assign a rank to each student's status records, latest first
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY `date-to` DESC) AS rn
    FROM your_table_name
    WHERE category = 'student'
),
active_on_june_2015 AS (
    SELECT DISTINCT id
    FROM your_table_name
    WHERE 
        category = 'student'
        AND status = 'active'
        -- Ensure target date falls within the status's valid period
        -- Adjust date conversion to match your database's syntax
        AND STR_TO_DATE('2015-06-01', '%Y-%m-%d') BETWEEN STR_TO_DATE(`date-from`, '%d/%m/%Y') AND STR_TO_DATE(`date-to`, '%d/%m/%Y')
)
SELECT 
    scs.id,
    scs.current_status
FROM student_current_status scs
JOIN active_on_june_2015 aoj ON scs.id = aoj.id
WHERE scs.rn = 1;

Approach 2: Correlated Subqueries (For Older SQL Databases) #

If your database doesn't support window functions, this method uses subqueries to achieve the same result:

SELECT 
    t1.id,
    t1.status AS current_status
FROM your_table_name t1
WHERE 
    t1.category = 'student'
    -- Get the latest status record for each student
    AND t1.`date-to` = (
        SELECT MAX(`date-to`) 
        FROM your_table_name t2 
        WHERE t2.id = t1.id AND t2.category = 'student'
    )
    -- Verify the student was active on June 1, 2015
    AND EXISTS (
        SELECT 1
        FROM your_table_name t3
        WHERE 
            t3.id = t1.id
            AND t3.category = 'student'
            AND t3.status = 'active'
            AND STR_TO_DATE('2015-06-01', '%Y-%m-%d') BETWEEN STR_TO_DATE(t3.`date-from`, '%d/%m/%Y') AND STR_TO_DATE(t3.`date-to`, '%d/%m/%Y')
    );

Key Notes #

• Date Handling: Your table uses dd/mm/yyyy format, so we use STR_TO_DATE() (for MySQL) to convert dates to a comparable format. Adjust this function for your database:
  • PostgreSQL: Use TO_DATE()
  • SQL Server: Use CONVERT()
• Current Status Logic: The record with the maximum date-to value is the current one, since 01/01/9999 indicates the status is still active.
• Filter Logic: The EXISTS clause (or the CTE in Approach 1) ensures we only include students who were actively enrolled on June 1, 2015—even if their status changed later.

Test Result with Your Sample Data #

Running either query on your sample data will return:

ID 2 is excluded because it's an employee, not a student.

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