sql - Oracle DB 中复杂的 SELECT 语句

Question

你能帮我解决一个复杂的选择语句吗？

我有一个这样的表:

+----+-----------+-----------+-----------------+
| 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 |
+----+-----------+-----------+-----------------+

我需要一个 select 语句，它将给我以下结果:User_name、Date_time_1(情况 1 的日期时间)、Date_time_3(情况 3 的日期时间)。

*在这种情况下，结果将只有 3 行(Alex 2 行，Johnny 1 行)。如上所述，每行将包含 3 列。

抱歉格式问题 - 我是通过手机发布的。当我到达 PC 时，我将添加结果表。*

输出应该是这样的:

+----+-----------+-------------+-----------------+
| ID | User_name |Date_time_1  |   Date_time_3   |
+----+-----------+-------------+-----------------+
|  1 | Alex      |14.3.18 11:30|   14.3.18 12:30 |
|  2 | Johnny    |15.3.18 10:01|   15.3.18 10:20 |
|  3 | Alex      |20.3.18 20:00|   20.3.18 21:25 |
+----+-----------+-------------+-----------------+

Answer 1

您可以使用条件聚合:

SELECT User_name,
      MAX(CASE WHEN Situation = 1 THEN Date_time END) AS date_time_1,
      MAX(CASE WHEN Situation = 3 THEN Date_time END) AS date_time_3
FROM tab
GROUP BY User_name;

编辑

In this case the result will have just 3 rows (2 for Alex and 1 for Johnny)

WITH cte AS (
   SELECT t.*, SUM(CASE WHEN Situation=1 THEN 1 ELSE 0 END)
          OVER(PARTITION BY User_name ORDER BY id) AS s
   FROM tab t
)
SELECT User_name,
      MAX(CASE WHEN Situation = 1 THEN Date_time END) AS date_time_1,
      MAX(CASE WHEN Situation = 3 THEN Date_time END) AS date_time_3
FROM cte
GROUP BY s, User_name;

DBFiddle Demo