Oracle查询：如何获取每日参与次数最多的用户对应subid

表结构与测试数据 #

建表语句：

create table employee
(
   name varchar2(10),
   sdate date,
   subid number
);

插入测试数据：

insert into employee values ('Arun',to_date('2016-03-01','YYYY-MM-DD'),123);
insert into employee values ('Arun',to_date('2016-03-01','YYYY-MM-DD'),453);
insert into employee values ('Raj',to_date('2016-03-01','YYYY-MM-DD'),12);
insert into employee values ('Raj',to_date('2016-03-01','YYYY-MM-DD'),45);
insert into employee values ('Raj',to_date('2016-03-01','YYYY-MM-DD'),16);
insert into employee values ('Raj',to_date('2016-03-01','YYYY-MM-DD'),18);
insert into employee values ('Darshan',to_date('2016-03-01','YYYY-MM-DD'),1600);
insert into employee values ('Darshan',to_date('2016-03-01','YYYY-MM-DD'),1820);

需求说明 #

每日统计每个员工的subid参与次数，需：

1. 找出当日参与次数最多的员工；
2. 若多个员工次数相同，选取字母顺序靠前的员工；
3. 最终返回该员工当日的最小subid及日期。

解决方案 #

使用CTE（公共表表达式）结合窗口函数实现，步骤清晰可读性强：

WITH name_daily_count AS (
    -- 第一步：按员工和日期分组，统计每日参与次数
    SELECT 
        name,
        sdate,
        COUNT(subid) AS participate_count
    FROM employee
    GROUP BY name, sdate
),
ranked_names AS (
    -- 第二步：对每个日期的员工按次数降序、字母升序排名，取排名第一的员工
    SELECT 
        name,
        sdate,
        participate_count,
        ROW_NUMBER() OVER (PARTITION BY sdate ORDER BY participate_count DESC, name ASC) AS rank_num
    FROM name_daily_count
)
-- 第三步：关联原表，取目标员工当日的最小subid
SELECT 
    rn.name,
    MIN(e.subid) AS subid,
    rn.sdate AS date
FROM ranked_names rn
JOIN employee e ON rn.name = e.name AND rn.sdate = e.sdate
WHERE rn.rank_num = 1
GROUP BY rn.name, rn.sdate;

代码解释 #

1. name_daily_count：按name和sdate分组，计算每个员工每日的subid参与次数；
2. ranked_names：通过PARTITION BY sdate将数据按日期分区，在每个分区内按participate_count降序、name升序排序，用ROW_NUMBER()生成排名，排名为1的就是符合条件的员工；
3. 最终查询：关联原表筛选出排名第一的员工，通过MIN(subid)获取其当日最小的subid，并按要求返回字段。

测试验证 #

• 示例1：原数据运行后，Raj的参与次数为4次（最多），返回结果：

name | subid | date
Raj  | 12    | 2016-03-01

• 示例2：若Darshan的参与次数也为4次，由于字母顺序Darshan早于Raj，返回结果：

name    | subid | date
Darshan | 1600  | 2016-03-01

（注：原示例2中subid写为18是笔误，实际Darshan的最小subid为1600）

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