+---------------+-------+-------+-------------------+
|ID_NOTIFICATION|CD_ETAT|TYP_MVT|DT_CAPT |
+---------------+-------+-------+-------------------+
|3111341 |AT |C |2019-06-12 00:03:37|
|3111341 |AN |M |2019-06-12 15:08:43|
|3111341 |AN |M |2019-06-12 15:10:11|
|3111341 |AN |M |2019-06-12 15:10:50|
|3111341 |AN |M |2019-06-12 15:11:34|
|3111341 |AN |M |2019-06-12 15:12:03|
|3111341 |AN |M |2019-06-12 15:14:04|
|3111341 |AN |M |2019-06-12 15:14:40|
|3111341 |AN |M |2019-06-12 15:15:22|
|3111341 |AN |M |2019-06-12 15:15:57|
|3111341 |AN |M |2019-06-12 15:25:28|
|3111341 |AN |M |2019-06-12 15:25:29|
|3111341 |AN |M |2019-06-12 15:27:50|
|3111341 |AN |M |2019-06-12 15:28:37|
|3111341 |AN |M |2019-06-12 15:32:22|
|3111341 |AN |M |2019-06-12 15:32:59|
|3111341 |EC |M |2019-06-12 15:33:04|
|3111341 |AN |M |2019-06-13 00:04:33|
|3111341 |TE |M |9999-01-01 00:00:00|
+---------------+-------+-------+-------------------+
如何在特定列上获得不同的行?
我试过:
select id_notification, cd_etat, min(dt_capt)
FROM lkr_send_notification
GROUP BY id_notification, cd_etat;
但它只会返回:
+---------------+-------+-------------------+
|ID_NOTIFICATION|CD_ETAT|DT_CAPT |
+---------------+-------+-------------------+
|3111341 |AN |2019-06-12 15:08:43|
|3111341 |AT |2019-06-12 00:03:37|
|3111341 |EC |2019-06-12 15:33:04|
|3111341 |TE |9999-01-01 00:00:00|
但我也希望结果包括:
|3111341 |AN |2019-06-13 00:04:33|
实际上,我需要得到cd上发生的每一个变化。
我在这里也要了同样的东西,不过是有Spark的。我现在愿意用sql来做。
1条答案
按热度按时间mccptt671#
使用
lag()
:这将返回状态更改的每一行。