标记先前在SQL中满足条件的行

Latent 发表于 Dev

潜

我需要标记来自用户的所有行，这些用户一旦完全填写了条件示例：

case when rank > 2 and clicks = 0 THEN [[[MARK ALL THE ROWS OF that user_id >= TIME]]] END 。

这是我想要桌子的样子。我不确定自己想做的事在里是否可行sql。

user_id Time    Clicks  Rank    Marking
2.30417E+13 10:39:03 AM 3   1   NULL
2.30417E+13 10:40:35 AM 0   1   NULL
2.30417E+13 10:40:36 AM 0   2   NULL**** All following rows should be marked
2.30417E+13 10:40:50 AM 1   1   Marked
2.30417E+13 10:41:20 AM 0   1   Marked
2.30417E+13 10:41:36 AM 0   2   Marked
2.30417E+13 10:42:41 AM 0   3   Marked
2.30417E+13 10:42:41 AM 0   4   Marked
2.30417E+13 10:44:23 AM 0   5   Marked
2.30417E+13 10:44:34 AM 0   6   Marked
2.30417E+13 10:44:36 AM 0   7   Marked
2.30417E+13 10:44:39 AM 0   8   Marked
2.30417E+13 10:44:44 AM 0   9   Marked
2.30417E+13 10:44:50 AM 0   10  Marked
2.30417E+13 10:44:53 AM 0   11  Marked
2.30417E+13 10:44:56 AM 0   12  Marked
2.30417E+13 10:45:05 AM 0   13  Marked
2.30417E+13 10:45:40 AM 1   1   Marked
2.30417E+13 10:45:41 AM 0   1   Marked
2.30417E+13 10:46:07 AM 0   2   Marked
2.30417E+13 10:46:07 AM 0   3   Marked
2.30417E+13 10:46:08 AM 0   4   Marked

谢谢你。

一点都没有

update (yourtable) 
   set marking='MARKED' 
 where (user_id, `time`) in ( 
        select user_id,`time`
          from (yourtable) yt,
                (select user_id,min(`time`)
                   from (yourtable)
                   where rank=2 and clicks=0
                group by user_id) minned
        where yt.user_id=minned.user_id and
            yt.`time`>minned.`time`
        )

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。