日期:2014-05-16  浏览次数:20831 次

一道sql面试题,求解答
tableA(地区表)
colA(地区id) colB(上级地区id)colC(地区名称)
tableB(人员情况表)
colD(人员ic) colE(人员名称) colA(所属地区) colG(人员成本) colH(性别)

1.全国所有人员成本最高的第10名到第20名。

2.所有人员成本总和大于2000的市,按照成本合计由高到低顺序。

3.输入一个地区id,在sql中使用?代替,查询这个地区所有本下级地区包含人数总和,输出格式:地区名称,人数。例如,输入国家id,则输出全国所有市区人数之和;输入北京id,则输出北京所有市区之和;输入海淀区id,则输出这个区人数之和。

4.假设tableA中数据固定为示例数据,性别1为男,0为女。查询输出:
              北京           上海
男           所有人数之和    所有人数之和
女           所有人数之和    所有人数之和

------解决方案--------------------
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
    &nbs