其实我有一个想法,就是直接row_number()over(order by 用户数 desc) rn,然后外层对rn做case when处理,我是想知道除了这个,有别的直接诶限定not in(全国,北十和南21)的写法么
with t as( select '全国' s,20 rs from dual union all select '北十',12 from dual union all select '南21',8 from dual union all select '天津',3 from dual union all select '北京',4 from dual union all select '广东',2 from dual ) select t.*, t1.r from t, (select t.s, row_number() over(order by rs desc) r from t where t.s not in ('全国', '北十', '南21')) t1 where t.s = t1.s(+) order by t1.r nulls first S RS R ---- ---------- ---------- 南21 8 全国 20 北十 12 北京 4 1 天津 3 2 广东 2 3
6 rows selected
用not in(全国,北十和南21)的条件先做row_number() over (order by 用户数 desc) 然后再union上in(全国,北十和南21)的数据不行吗?
其实就是说,我能不能row_number()over(限定省份不等于全国北十省和南21 order by 用户数 desc)
SELECT t.*, CASE WHEN t.s IN ('全国', '北十', '南21') THEN NULL ELSE ROW_NUMBER() OVER(PARTITION BY CASE WHEN t.s IN ('全国','北十','南21') THEN NULL ELSE 1 END ORDER BY t.rs DESC) END FROM t ORDER BY 2 DESC,3 NULLS FIRST
with t as(
select '全国' s,20 rs from dual
union all
select '北十',12 from dual
union all
select '南21',8 from dual
union all
select '天津',3 from dual
union all
select '北京',4 from dual
union all
select '广东',2 from dual
)
select t.*, t1.r
from t,
(select t.s, row_number() over(order by rs desc) r
from t
where t.s not in ('全国', '北十', '南21')) t1
where t.s = t1.s(+)
order by t1.r nulls first
S RS R
---- ---------- ----------
南21 8
全国 20
北十 12
北京 4 1
天津 3 2
广东 2 3
6 rows selected
然后再union上in(全国,北十和南21)的数据不行吗?
其实就是说,我能不能row_number()over(限定省份不等于全国北十省和南21 order by 用户数 desc)
SELECT t.*,
CASE WHEN t.s IN ('全国', '北十', '南21') THEN NULL
ELSE ROW_NUMBER() OVER(PARTITION BY CASE WHEN t.s IN ('全国','北十','南21') THEN NULL ELSE 1 END ORDER BY t.rs DESC)
END
FROM t
ORDER BY 2 DESC,3 NULLS FIRST