如何用SQL按天统计符合特定场景的员工（资源）数量？

Hey there! Let's work through this problem together. First, let's recap the core requirements to make sure we're aligned:

• An employee can be part of multiple projects
• They submit separate time entries for each project on the same day
• When counting how many unique employees we have per day, we need to count each person only once, no matter how many projects they logged time for that day.

假设我们的表结构 #

Let's start with a common table setup for time entries (let's call it time_entries):

CREATE TABLE time_entries (
    employee_id INT,    -- 员工ID
    project_id INT,     -- 项目ID
    entry_date DATE,    -- 工时提交日期
    hours_worked DECIMAL(5,2)  -- 工作时长
);

示例数据 #

Here's some sample data that matches your scenario:

INSERT INTO time_entries VALUES
(1, 101, '2024-05-01', 4),  -- 员工1，项目101，5月1日
(1, 102, '2024-05-01', 4),  -- 员工1，项目102，同一天
(2, 101, '2024-05-01', 8),  -- 员工2，项目101，5月1日
(1, 101, '2024-05-02', 6),  -- 员工1，项目101，5月2日
(3, 103, '2024-05-02', 8);  -- 员工3，项目103，5月2日

Notice that employee 1 has two entries on 2024-05-01 (for different projects) — we want this to count as 1 in the daily total, not 2.

解决方案1：用DISTINCT + GROUP BY（最简单直接） #

This is the most straightforward approach. We group by the date, then use COUNT(DISTINCT employee_id) to count only unique employees per day:

SELECT
    entry_date,
    COUNT(DISTINCT employee_id) AS unique_employee_count
FROM time_entries
GROUP BY entry_date
ORDER BY entry_date;

怎么生效的？ #

The COUNT(DISTINCT employee_id) clause looks at all employee IDs in each date group, removes duplicates, then counts the remaining unique values. Exactly what we need!

解决方案2：用窗口函数（更灵活的扩展方案） #

If you need to add more logic later (like showing how many projects each employee worked on that day), window functions are a great choice. We first mark the first entry for each employee per day, then count those:

WITH daily_unique_employees AS (
    SELECT
        entry_date,
        employee_id,
        -- 给每个员工每天的记录编号，第一条是1
        ROW_NUMBER() OVER (PARTITION BY entry_date, employee_id ORDER BY project_id) AS entry_rank
    FROM time_entries
)
SELECT
    entry_date,
    COUNT(employee_id) AS unique_employee_count
FROM daily_unique_employees
WHERE entry_rank = 1  -- 只保留每个员工每天的第一条记录
GROUP BY entry_date
ORDER BY entry_date;

This gives the same result as the first method, but gives you more room to add details (like project counts) if needed later.

验证结果 #

Both queries will return this output for our sample data:

Which is exactly what we want: 2 unique employees on May 1st (employee 1 and 2), and 2 on May 2nd (employee 1 and 3).

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