查询SQL求解

Question

建个测试表：
create table t_num(id integer,num number);
insert into t_num values(1,100);
insert into t_num values(2,90);
insert into t_num values(3,0);
insert into t_num values(4,10);
insert into t_num values(5,20);
insert into t_num values(6,50);
insert into t_num values(7,0);
insert into t_num values(8,10);
insert into t_num values(9,20);
commit;

表内容如下：
SQL> select * from t_num;
        ID        NUM
---------- ----------
         1        100
         2         90
         3          0
         4         10
         5         20
         6         50
         7          0
         8         10
         9         20

要求：记录按顺序显示，但只显示最后一个NUM为0记录后面的记录（不显示0记录）。这样一个查询SQL该如何写。数据库为Oracle数据库。一般情况下记录不多。


想了一个办法，不太好，有没有人有更好的办法？
select id,num from (
    select id,num,
         sum(case when num=0 then 0 else 1 end) over(order by id desc rows between unbounded preceding and 0 following)
         - row_number() over (order by id desc) judge_code
    from t_num
) where judge_code=0 order by id

Answer 1

select *
from t_num t
where t.id > (select max(id) from t_num tn where tn.num = 0);