SQL> select * from tuser;NAME AGE ADDR
---------- ---------- --------------------
aa 10 addr01
aa 20 addr01
ab 30 addr01
bb 30 addr02
bc 35 addr02SQL> select * from (
2 select name,age,addr,rank() over(partition by addr order by age desc) as n
3 from tuser) where n=1;NAME AGE ADDR N
---------- ---------- -------------------- ----------
ab 30 addr01 1
bc 35 addr02 1SQL>
---------- ---------- --------------------
aa 10 addr01
aa 20 addr01
ab 30 addr01
bb 30 addr02
bc 35 addr02SQL> select * from (
2 select name,age,addr,rank() over(partition by addr order by age desc) as n
3 from tuser) where n=1;NAME AGE ADDR N
---------- ---------- -------------------- ----------
ab 30 addr01 1
bc 35 addr02 1SQL>
tab a Where tab.lodging =a.lodging);
帮忙在给解释一下这条语句,它也能实现上述功能,可这个查询过程确实一点也想不通!!
请高手多指点!!!谢谢了!!!
根据这一条的lodging字段的值 到 tab里面找到 lodging值=当前值的最大的age然后判断当前行的age值是否等于取到的max(age),如果相等则符合要求,显示出来,否则不显示
tab a Where tab.lodging =a.lodging);
括号里的SELECT 选出每个地区最大的年龄。
然后括号外的选出对应的所有信息。
from tab
where tab.age,tab.lodging in (select max(aa.age),aa.lodging from tab aa group by aa.lodging)
这下你想明白了吧