Can you please help me with one complicated select statement?
你能帮我做一个复杂的选择语句吗?
I have a table like this:
我有一张这样的桌子:
+----+-----------+-----------+-----------------+
| 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 |
+----+-----------+-----------+-----------------+
And I need a select statement, which will give me the following result: User_name, Date_time_1 (Date_time of situation 1), Date_time_3 (Date_time of situation 3).
我需要一个select语句，它将给出以下结果:User_name、Date_time_1(情形1的Date_time)、Date_time_3(情形3的Date_time)。
*In this case the result will have just 3 rows (2 for Alex and 1 for Johnny). Each row will contain 3 columns as described above.
*在这种情况下，结果只有3行(Alex 2行，Johnny 1行)。如上所述，每一行将包含3列。
And sorry for the formatting - I posted that from a mobile. I will add the result table when I will get to PC.*
不好意思，我是从手机上发的。我将添加结果表，当我到达PC。*。
That's how the output should looks like:
这就是输出的样子:
+----+-----------+-------------+-----------------+
| 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 |
+----+-----------+-------------+-----------------+
1 个解决方案
#1
2
You could use conditional aggregation:
您可以使用条件聚合:
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;
EDIT
In this case the result will have just 3 rows (2 for Alex and 1 for Johnny)
在这种情况下，结果只有3行(Alex 2行，Johnny 1行)
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演示
#1
2
You could use conditional aggregation:
您可以使用条件聚合:
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;
EDIT
In this case the result will have just 3 rows (2 for Alex and 1 for Johnny)
在这种情况下，结果只有3行(Alex 2行，Johnny 1行)
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演示