表1:省表
字段:省ID,省名称
表2:市表
字段:市名称,市编号
表3:关系表
字段:省编号,市编号
表4:风险值表
字段:市编号,市风险值业务逻辑:我现在想查询全国所有省风险值在前5的市,记录类似于下边这种的SQL需要如何写,希望大侠给予指点,谢谢!
比如:
A省ID,A1市ID,风险值5
A省ID,A2市ID,风险值4
A省ID,A3市ID,风险值4
A省ID,A4市ID,风险值4
A省ID,A5市ID,风险值3
B省ID,B1市ID,风险值5
B省ID,B2市ID,风险值4
B省ID,B3市ID,风险值4
B省ID,B4市ID,风险值4
B省ID,B5市ID,风险值3
字段:省ID,省名称
表2:市表
字段:市名称,市编号
表3:关系表
字段:省编号,市编号
表4:风险值表
字段:市编号,市风险值业务逻辑:我现在想查询全国所有省风险值在前5的市,记录类似于下边这种的SQL需要如何写,希望大侠给予指点,谢谢!
比如:
A省ID,A1市ID,风险值5
A省ID,A2市ID,风险值4
A省ID,A3市ID,风险值4
A省ID,A4市ID,风险值4
A省ID,A5市ID,风险值3
B省ID,B1市ID,风险值5
B省ID,B2市ID,风险值4
B省ID,B3市ID,风险值4
B省ID,B4市ID,风险值4
B省ID,B5市ID,风险值3
from
(select row_number() over(partition by cityid order by fxValue desc) rn,cityid,fxValue from tb) a
left join pc on a.cityid=pc.cityid
left join provice p on pc.proviceid=p.proviceid
where rn<6
with t1 as
(
select 1 s_id,'湖北省' s_name from dual union all
select 2 s_id,'湖南省' s_name from dual
),t2 as
(
select 1 sid,'武汉' sname from dual union all
select 2 sid,'襄阳' sname from dual union all
select 3 sid,'荆州' sname from dual union all
select 4 sid,'孝感' sname from dual union all
select 5 sid,'长沙' sname from dual union all
select 6 sid,'株洲' sname from dual union all
select 7 sid,'重庆' sname from dual
),t3 as
(
select 1 c1,1 c2 from dual union all
select 1 c1,2 c2 from dual union all
select 1 c1,3 c2 from dual union all
select 1 c1,4 c2 from dual union all
select 2 c1,5 c2 from dual union all
select 2 c1,6 c2 from dual union all
select 2 c1,7 c2 from dual
),t4 as
(
select 1 sid,50 fx from dual union all
select 2 sid,24 fx from dual union all
select 3 sid,77 fx from dual union all
select 4 sid,55 fx from dual union all
select 5 sid,85 fx from dual union all
select 6 sid,24 fx from dual union all
select 7 sid,1 fx from dual
)select s_name,sname,fx
from
(
select t1.s_name,t2.sname,t4.fx,row_number() over(partition by t1.s_id order by t4.fx desc) rn
from t1,t2,t3,t4
where t1.s_id = t3.c1 and t2.sid = t3.c2 and t2.sid = t4.sid
)
where rn <= 2
s_name sname fx
----------------------------------
1 湖北省 荆州 77
2 湖北省 孝感 55
3 湖南省 长沙 85
4 湖南省 株洲 24
也许他得考虑并列select s_name, sname, fx
from (select t1.s_name,
t2.sname,
t4.fx,
dense_rank() over(partition by t1.s_id order by t4.fx desc) rn
from t1, t2, t3, t4
where t1.s_id = t3.c1
and t2.sid = t3.c2
and t2.sid = t4.sid)
where rn <= 2