1.全国所有人员成本最高的第10名到第20名。
SELECT *
FROM (
SELECT colE(人员名称), colG(人员成本), row_number() over (ORDER BY colG(人员成本)) AS seq
FROM tableB
)
WHERE seq >= 10 AND seq <= 20
2.所有人员成本总和大于2000的市,按照成本合计由高到低顺序。
SELECT colC, colg
FROM (
SELECT a.colC, SUM(b.colG) AS colg
FROM tableB b
, tableA a
WHERE a.colA = b.colA
GROUP BY a.colC
)
WHERE colg > 2000
ORDER BY colg DESC;
3.输入一个地区id,在sql中使用?代替,查询这个地区所有本下级地区包含人数总和,输出格式:地区名称,人数。例如,输入国家id,则输出全国所有市区人数之和;输入北京id,则输出北京所有市区之和;输入海淀区id,则输出这个区人数之和。SELECT t.*
, (
SELECT SUM(b.colG) AS colg
FROM tableB b
, (
SELECT t.*, LEVEL
FROM tableA t
WHERE LEVEL >= 2
START WITH colA = 地区ID
CONNECT BY PRIOR colA = colB
) a
WHERE a.colA = b.colA
) AS colg
FROM tableA t
WHERE colB = 地区ID4.假设tableA中数据固定为示例数据,性别1为男,0为女。查询输出:
北京 上海
男 所有人数之和 所有人数之和
女 所有人数之和 所有人数之和
SELECT sex
, CASE sex
WHEN '男' THEN
(
SELECT SUM(b.colG) AS colg
FROM tableB b
, (
SELECT t.*, LEVEL
FROM tableA t
WHERE LEVEL >= 2
START WITH colA = 北京ID
CONNECT BY PRIOR colA = colB
) a
WHERE a.colA = b.colA
AND b.colH = '男'
)
WHEN '女' THEN
(
SELECT SUM(b.colG) AS colg
FROM tableB b
, (
SELECT t.*, LEVEL
FROM tableA t
WHERE LEVEL >= 2
START WITH colA = 北京ID
CONNECT BY PRIOR colA = colB
) a
WHERE a.colA = b.colA
AND b.colH = '女'
)
ELSE NULL
END AS 北京
, --和上面类似语句,数据可以用函数实现
FROM (
SELECT '男' AS sex, 1 AS seq FROM dual
UNION ALL
SELECT '女' AS sex, 2 AS seq FROM dual
)
ORDER BY seq;
注:仅供参考,因为实际存储数据的关系并没有描述很细。大致如此,至于细节可以按实际情况更改代码即可。
SELECT *
FROM (
SELECT colE(人员名称), colG(人员成本), row_number() over (ORDER BY colG(人员成本)) AS seq
FROM tableB
)
WHERE seq >= 10 AND seq <= 20
2.所有人员成本总和大于2000的市,按照成本合计由高到低顺序。
SELECT colC, colg
FROM (
SELECT a.colC, SUM(b.colG) AS colg
FROM tableB b
, tableA a
WHERE a.colA = b.colA
GROUP BY a.colC
)
WHERE colg > 2000
ORDER BY colg DESC;
3.输入一个地区id,在sql中使用?代替,查询这个地区所有本下级地区包含人数总和,输出格式:地区名称,人数。例如,输入国家id,则输出全国所有市区人数之和;输入北京id,则输出北京所有市区之和;输入海淀区id,则输出这个区人数之和。SELECT t.*
, (
SELECT SUM(b.colG) AS colg
FROM tableB b
, (
SELECT t.*, LEVEL
FROM tableA t
WHERE LEVEL >= 2
START WITH colA = 地区ID
CONNECT BY PRIOR colA = colB
) a
WHERE a.colA = b.colA
) AS colg
FROM tableA t
WHERE colB = 地区ID4.假设tableA中数据固定为示例数据,性别1为男,0为女。查询输出:
北京 上海
男 所有人数之和 所有人数之和
女 所有人数之和 所有人数之和
SELECT sex
, CASE sex
WHEN '男' THEN
(
SELECT SUM(b.colG) AS colg
FROM tableB b
, (
SELECT t.*, LEVEL
FROM tableA t
WHERE LEVEL >= 2
START WITH colA = 北京ID
CONNECT BY PRIOR colA = colB
) a
WHERE a.colA = b.colA
AND b.colH = '男'
)
WHEN '女' THEN
(
SELECT SUM(b.colG) AS colg
FROM tableB b
, (
SELECT t.*, LEVEL
FROM tableA t
WHERE LEVEL >= 2
START WITH colA = 北京ID
CONNECT BY PRIOR colA = colB
) a
WHERE a.colA = b.colA
AND b.colH = '女'
)
ELSE NULL
END AS 北京
, --和上面类似语句,数据可以用函数实现
FROM (
SELECT '男' AS sex, 1 AS seq FROM dual
UNION ALL
SELECT '女' AS sex, 2 AS seq FROM dual
)
ORDER BY seq;
注:仅供参考,因为实际存储数据的关系并没有描述很细。大致如此,至于细节可以按实际情况更改代码即可。
解决方案 »
- 求高手:oracle中的子查询中为什么不能有order by
- 表关联取值问题!
- Proc的error:error: stray '\129' in program 是什么意思?
- 如何实现下面的查询语句?
- 大家晚上好。用PowerDesigner生成Oracle脚本,所有表名和字段名都有引号,生成后无法访问。怎么解决?
- 日期比较,请看内容。谢谢!
- Procedure 中如何将一个表的内容全部插入另一结构相同的表?
- 难倒一片大神的sql问题
- 100分 保存一篇几千字的文章(是HTML标示)用什么字段?如何保证保存和读出不是乱码呢?
- SQL语句查原因
- 谁有oracle 10.2.0.0.0版本 下载
- Oracle select 字符串处理
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的思路:
--首先查出每个地区不同性别的人数,在行转列(由于题目很明显告诉你固定数据)