17,377
社区成员
发帖
与我相关
我的任务
分享
select * from (
select colD,colE,dense_rank() over(order by colG desc) as rk from tableb
) where rk between 10 and 20
--当然面试的话这里可以注明 dense_rank 和 rank区别等等。
第二题,2楼写的就可以了
第三题,注意题目是人数之和
select count(*) as cnt from tableB where colA in (
select colA from tableA
start with colA = 传入id
connect by prior colA = colB
)
第四题
--测试数据
with tableA as (
select 1 as colA,0 as colB,'北京' as colC from dual
union all
select 2 as colA,0 as colB,'上海' from dual
),
tableB as(
select 1 as colD,'人员1' as colE,1 as colA,2000 as colG,0 as colH from dual
union all
select 2,'人员2',1,2000,0 from dual
union all
select 3,'人员3',1,2000,0 from dual
union all
select 4,'人员4',1,2000,0 from dual
union all
select 5,'人员5',1,2000,1 from dual
union all
select 6,'人员6',2,2000,1 from dual
union all
select 7,'人员7',1,2000,1 from dual
)
--查询sql
select decode(t.colH,0,'女','男') as gender,
max(decode(t.colC,'北京',t.cnt,0)) as 北京,
max(decode(t.colC,'上海',t.cnt,0)) as 上海
from (
select a.colC,b.colH,count(*) as cnt from tableB b
left join tableA a on a.colA = b.colA
group by colC,colH
) t group by t.colH
--结果
GENDER 北京 上海
------ ---------- ----------
男 2 1
女 4 0
--这里可以解释下本sql的思路:
--首先查出每个地区不同性别的人数,在行转列(由于题目很明显告诉你固定数据)