【SQL练习】一个简单SQL面试题

有一个员工入职的日志表，包括员工id、入职日期 2列，求出每年入职人数、历史截止每年的入职人数，用Oracle SQL写出来

根据您的需求，可以使用Oracle SQL的窗口函数来实现。以下是两种写法：

方法一：使用窗口函数（推荐）

SELECT 
    EXTRACT(YEAR FROM hire_date) AS year,
    COUNT(*) AS annual_hire_count,
    COUNT(*) OVER (ORDER BY EXTRACT(YEAR FROM hire_date)) AS cumulative_hire_count
FROM employee_hire_log
GROUP BY EXTRACT(YEAR FROM hire_date)
ORDER BY year;

方法二：使用子查询和连接

WITH annual_stats AS (
    SELECT 
        EXTRACT(YEAR FROM hire_date) AS year,
        COUNT(*) AS annual_hire_count
    FROM employee_hire_log
    GROUP BY EXTRACT(YEAR FROM hire_date)
)
SELECT 
    a.year,
    a.annual_hire_count,
    (SELECT SUM(b.annual_hire_count) 
     FROM annual_stats b 
     WHERE b.year <= a.year) AS cumulative_hire_count
FROM annual_stats a
ORDER BY a.year;

方法三：考虑数据表结构和完整示例

如果您的表结构如下：

-- 假设表结构
CREATE TABLE employee_hire_log (
    employee_id NUMBER,
    hire_date DATE
);

完整的查询示例：

SELECT 
    TO_CHAR(hire_date, 'YYYY') AS hire_year,
    COUNT(employee_id) AS annual_hire_count,
    SUM(COUNT(employee_id)) OVER (ORDER BY TO_CHAR(hire_date, 'YYYY')) AS cumulative_hire_count
FROM employee_hire_log
GROUP BY TO_CHAR(hire_date, 'YYYY')
ORDER BY hire_year;

包含测试数据的完整示例

-- 测试数据
WITH employee_hire_log AS (
    SELECT 1 AS employee_id, DATE '2020-01-15' AS hire_date FROM DUAL UNION ALL
    SELECT 2, DATE '2020-03-20' FROM DUAL UNION ALL
    SELECT 3, DATE '2021-02-10' FROM DUAL UNION ALL
    SELECT 4, DATE '2021-06-25' FROM DUAL UNION ALL
    SELECT 5, DATE '2021-08-30' FROM DUAL UNION ALL
    SELECT 6, DATE '2022-04-12' FROM DUAL
)
-- 主查询
SELECT 
    TO_CHAR(hire_date, 'YYYY') AS hire_year,
    COUNT(employee_id) AS annual_hire_count,
    SUM(COUNT(employee_id)) OVER (ORDER BY TO_CHAR(hire_date, 'YYYY')) AS cumulative_hire_count
FROM employee_hire_log
GROUP BY TO_CHAR(hire_date, 'YYYY')
ORDER BY hire_year;

输出结果示例：

HIRE_YEAR | ANNUAL_HIRE_COUNT | CUMULATIVE_HIRE_COUNT
----------|------------------|---------------------
2020     | 2                | 2
2021     | 3                | 5
2022     | 1                | 6

推荐使用方法一，因为它：

• 代码简洁易懂
• 使用窗口函数，性能较好
• 直接在一个查询中完成所有计算