Oracle我还未入门,写一个SQL Server中能通过的供参考:
select * from tablename TA inner join (slect a,b,max(d) as md from tablename group by a,b) as TB on TA.a=TB.a and TA.b=TB.b and TA.d=TB.md
select * from tablename TA inner join (slect a,b,max(d) as md from tablename group by a,b) as TB on TA.a=TB.a and TA.b=TB.b and TA.d=TB.md
where tbname.a=t.a and tbname.b=t.b and tbname.d=tb.d
group by a,b,d;
( select a,b,max(d) max_d from yourtable group by a,b) tab2
where tab1.a=tab2.a and tab1.b=tab2.b and tab1.d=tab2.max_d
group by tab1.a,tab1.b,tab1.d
我还没有试过你上面的SQL语句,不过我对它有一点疑问,
select * from tablename TA是选择了全部,
那么就应该满足“D相同时随机取一个“的条件
-------------------------------------------------------------------------
我的MSN: [email protected]。欢迎加我。我想从头学Oracle,需要大家的支持。
(select a,b,c,d,rank(d) over(partition by a,b order by d desc) rk from tab1)
where rk=1 group by a,b,d
应该这样:
select test1.a,test1.b,test1.c,test1.d
from test1,(select a,b,max(d) as md from test1 group by a,b) as test2
where (test1.a=test2.a)
and (test1.b=test2.b)
and (test1.d=test2.md)
但是没关系,我们就查询表中test1.a=test2.a;test1.b=test2.b;test1.d=test2.md,就可以得到你的结果了,我已经测试过正确无误。
看了几为给出的解决方法之后,
我考虑到在本应用中d的取值范围是1—9,
所以我这样做:
select a,b,max(TO_CHAR(d)||c) as dc from tablename group by a,b
取到结果之后再把dc拆开来用。