例如:找出以下表格欄位TEST1.L為偶數的資料列, 並給予編號如下,
select rownum, L, P, Q from test1 where mod(L,2)=0;
ROWNUM L P Q
--------- --------- - ---------
1 2 B 2
2 4 C 3
3 6 A 3
4 8 G 3
其實也可以用以下SQL語法,達到相同的目的,
select count(y.L) my_rownum, x.L, x.P, x.Q
from test1 x, test1 y
where x.L >= y.L
and mod(x.L,2)=0 --->
and mod(y.L,2)=0 ---> 注意:x,y條件皆相同
group by x.L, x.P, x.Q;
MY_ROWNUM L P Q
--------- --------- - ---------
1 2 B 2
2 4 C 3
3 6 A 3
4 8 G 3
PS:這種自身join的方法,也或許可以用在其他資料庫或不支援rownum的情況.
再加一個範例: 相同x.cus_id, 按照 x.risk,x.bra_id,x.pr_rank_name,x.pro_name 排名
select count(y.cus_id) as num,
x.cus_id,x.risk,x.bra_id,x.pr_rank_name,x.pro_name
from aaa x, aaa y
where x.cus_id = y.cus_id
and ((x.risk > y.risk) or
((x.risk = y.risk) and (x.bra_id > y.bra_id)) or
((x.risk = y.risk) and (x.bra_id = y.bra_id) and (x.pr_rank_name > y.pr_rank_name)) or
((x.risk = y.risk) and (x.bra_id = y.bra_id) and (x.pr_rank_name = y.pr_rank_name) and (x.pro_name > y.pro_name )) or
((x.risk = y.risk) and (x.bra_id = y.bra_id) and (x.pr_rank_name = y.pr_rank_name) and (x.pro_name = y.pro_name ))
)
group by x.cus_id,x.risk,x.bra_id,x.pr_rank_name,x.pro_name
