跳过具有相同值的第一个连续记录，然后停止

Question

这是我的表格场景

+----------+------------+---------------------+------------------+
| userd_id | user_value |    user_datetime    | i_want_to_select |
+----------+------------+---------------------+------------------+
|        1 |          1 | 2020-01-31 12:13:14 |                  |
|        2 |          1 | 2020-01-30 12:13:14 |                  |
|        3 |          1 | 2020-01-29 12:13:14 |                  |
|        4 |          2 | 2020-01-28 12:13:14 |                  |
|        5 |          2 | 2020-01-27 12:13:14 |                  |
|        6 |          3 | 2020-01-20 12:13:14 |                  |
|        7 |          1 | 2020-01-19 12:13:14 | this             |
|        8 |          1 | 2020-01-18 12:13:14 | this             |
|        9 |          1 | 2020-01-17 12:13:14 | this             |
+----------+------------+---------------------+------------------+

需要跳过那些连续的值，只有IF第一个元素是1，​​并在值不同时立即停止

我用PHP代码来做，但我想避免加重PHP处理器和垃圾收集器的负担

foreach ($rows as &$row){
if($row['user_value'] === 1){
unset($row); // remove my row
} else {
break; // at the first different value, stop
}
}
unset($row);
$rows = array_values($rows) //reset the array index/key

感谢

Answer 1

一种方法是：

select t.*
from t
where t.user_datetime >= (select min(t2.user_datetime)
from t t2
where t2.user_id = t.user_id and
t2.user_value <> 1
);

您也可以使用窗口功能，例如：

select t.*
from (select t.*,
min( case when t.user_value <> 1 then t user_datetime) over (partition by t.user_id) as min_not1_datetime
from t
) t
where user_datetime >= min_not1_datetime;