Select workage
From (
Select jb.work_age workage,Count(jb.work_age) num From personel_tb ps,JOBSEEKER_BASIC jb Where ps.SeekerID=jb.Id And ps.menberid=142 Group By jb.work_age) s
Where s.num=(Select Max(num) From (Select jb.work_age workage,Count(jb.work_age) num From personel_tb ps,JOBSEEKER_BASIC jb Where ps.SeekerID=jb.Id And ps.menberid=142 Group By jb.work_age))其中workage是工作年限。我用这样的sql能够查询出我想要的结果,不知道有没有更好点的写法,求教
From (
Select jb.work_age workage,Count(jb.work_age) num From personel_tb ps,JOBSEEKER_BASIC jb Where ps.SeekerID=jb.Id And ps.menberid=142 Group By jb.work_age) s
Where s.num=(Select Max(num) From (Select jb.work_age workage,Count(jb.work_age) num From personel_tb ps,JOBSEEKER_BASIC jb Where ps.SeekerID=jb.Id And ps.menberid=142 Group By jb.work_age))其中workage是工作年限。我用这样的sql能够查询出我想要的结果,不知道有没有更好点的写法,求教
from (select workage, num, row_number() over(order by num desc) rn
from (Select jb.work_age workage, Count(jb.work_age) num
From personel_tb ps, JOBSEEKER_BASIC jb
Where ps.SeekerID = jb.Id
And ps.menberid = 142
Group By jb.work_age))
where rn = 1;
好象也比较笨,不知道还有什么好方法不?
from ( Select jb.work_age workage,Count(jb.work_age) num,
From personel_tb ps,JOBSEEKER_BASIC jb
Where ps.SeekerID=jb.Id And ps.menberid=142
and rownum = 1
Group By jb.work_age
order by Count(jb.work_age) desc
) a
SELECT WORKAGE
FROM (SELECT JB.WORK_AGE WORKAGE, COUNT(JB.WORK_AGE) NUM
FROM PERSONEL_TB PS, JOBSEEKER_BASIC JB
WHERE PS.SEEKERID = JB.ID
AND PS.MENBERID = 142
GROUP BY JB.WORK_AGE
ORDER BY 2 DESC)TT
WHERE ROWNUM = 1;
t.workage
from
(
select jb.work_age workage,
row_number() over(partition by jb.work_age order by jb.work_age desc) r
from personel_tb ps, JOBSEEKER_BASIC jb
Where ps.SeekerID = jb.Id
And ps.menberid = 142
) t
where t.r=1