题目:表名employee
表名:office
题1:查出每个办公室薪水最高的员工
sql如下:(由于这个需要在hive中运行,我没有启动hive,代码比较简单就在这里演示了)
SELECT o.Name,t.Name
FROM office o LEFT JOIN
(SELECT *,row_number() over(PARTITION BY Eid ORDER BY Salary DESC)AS num FROM employee)t
ON o.Eid=t.Eid
WHERE t.num=1
题2:求出下列格式的数据
sql代码如下
SELECT
(CASE
WHEN Salary<10000 THEN '1万以下'
WHEN salary>=10000 AND salary<=30000 THEN '1万-3万'
ELSE '3万以上' END) LEVEL,
COUNT(1) AS COUNT,
CONCAT(CONVERT(COUNT(1)/(SELECT COUNT(1)FROM employee)*100,DECIMAL(4,0)),'%') Percetage
FROM employee
GROUP BY LEVEL
启发:之前用分类函数,但是没有想到可以在后面进一步group by ,所以这个想法比较靠谱,易于实现
下面还有一种比较复杂的也能实现。
SELECT t.Level,COUNT(1) AS COUNT, CONCAT(CONVERT(COUNT(1)/(SELECT COUNT(1) FROM employee)*100,DECIMAL(4,0)),'%')AS Percentage
FROM
(SELECT
(CASE
WHEN Salary<10000 THEN '1万以下'
WHEN Salary>=10000 AND Salary<=30000 THEN '1-3万'
ELSE '3万以上' END) LEVEL
FROM employee)t
GROUP BY t.Level