Oracle复杂SELECT语句需求：提取同用户不同Situation对应时间

嘿，我来帮你搞定这个Oracle查询需求！你需要把每个用户的每一组完整流程（Situation从1到3）里，对应1的时间和3的时间配对展示，而且要区分同一用户的不同流程批次（比如Alex有两次这样的流程，所以结果里有两行他的数据）。

首先先确认下你的源表数据（假设表名为user_situations）：

SELECT ID, User_name, Situation, Date_time 
FROM user_situations;

查询结果：

+----+-----------+-----------+-----------------+
| ID | User_name | Situation | Date_time       |
+----+-----------+-----------+-----------------+
| 1  | Alex      | 1         | 14.3.18 11:30   |
| 4  | Alex      | 2         | 14.3.18 11:35   |
| 6  | Alex      | 3         | 14.3.18 12:30   |
| 7  | Johnny    | 1         | 15.3.18 10:01   |
| 9  | Johnny    | 2         | 15.3.18 10:05   |
| 12 | Johnny    | 3         | 15.3.18 10:20   |
| 14 | Alex      | 1         | 20.3.18 20:00   |
| 15 | Alex      | 2         | 20.3.18 20:25   |
| 17 | Alex      | 3         | 20.3.18 21:25   |
+----+-----------+-----------+-----------------+

解决方案：使用窗口函数分组流程批次 #

我们可以用COUNT()窗口函数来给每个用户的每一组流程标记一个批次号，然后通过这个批次号把Situation 1和3的记录关联起来。具体SQL如下：

SELECT 
    ROW_NUMBER() OVER (ORDER BY us.User_name, us.batch_num) AS ID,
    us.User_name,
    MAX(CASE WHEN us.Situation = 1 THEN us.Date_time END) AS Date_time_1,
    MAX(CASE WHEN us.Situation = 3 THEN us.Date_time END) AS Date_time_3
FROM (
    SELECT 
        ID,
        User_name,
        Situation,
        Date_time,
        -- 给每个用户的每一组流程分配批次号：每遇到Situation=1就递增
        COUNT(CASE WHEN Situation = 1 THEN 1 END) OVER (
            PARTITION BY User_name 
            ORDER BY Date_time 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS batch_num
    FROM user_situations
) us
GROUP BY us.User_name, us.batch_num
ORDER BY us.User_name, us.batch_num;

执行结果 #

这个查询会输出你期望的结果：

代码解释 #

1. 子查询中的窗口函数：COUNT(CASE WHEN Situation = 1 THEN 1 END) OVER (...) 会对每个用户按时间顺序遍历记录，每遇到一个Situation=1的记录，就给当前及之后的记录（直到下一个Situation=1）分配一个递增的批次号，这样同一流程的1、2、3记录会有相同的batch_num。
2. 主查询的聚合：通过GROUP BY User_name, batch_num把同一批次的记录聚合，然后用MAX(CASE...)分别提取Situation=1和3对应的时间（因为每个批次里只有一条1和一条3，MAX和MIN效果一样）。
3. ROW_NUMBER()：用来生成结果里的ID列，按用户名和批次号排序。

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