select empid
from (select empid, avg(hours) as avghours
from workload
group by empid) c
where c.avghours>= (select max(avghours)
from (select empid, avg(hours) as avghours
from workload
group by empid))
用了两次相同的子查询,
谁能用简洁的一条sql搞定呢,麻烦献上一条哦,感谢!!! ------最佳解决方案--------------------
正解,
分析函数,改成rank更好吧,可以查询出并列的。 ------其他解决方案-------------------- SELECT empid
FROM (SELECT AVG(hours), empid
FROM workload
GROUP BY empid
ORDER BY AVG(hours) DESC)
WHERE ROWNUM = 1
------其他解决方案--------------------
select empid
from(
select empid,
avg(hours),
row_number()over(order by avg(hours) desc) as rn
from workload
group by empid
)where rn = 1 -- rn = 1 平均时长第一,rn=2 平均时长第二 通用不?