有这样一张表 t1 (用的是oracle)
--------------------------------id content version
1 11 0.1
2 11 1.0
3 22 1.0
4 22 2.0
5 22 0.1
6 33 3.0
7 33 4.0 ---------------------------------
希望得到的结果是这样的id content version
2 11 1.0
4 22 2.0
7 33 4.0
---------------------------------我想选出在各个content里 version最大的记录,我现在写的是
select content,max(version) group by version
,这样的话,不能把id 也选出来,有什么好一点的办法可以得到如上的结果,谢谢了!
--------------------------------id content version
1 11 0.1
2 11 1.0
3 22 1.0
4 22 2.0
5 22 0.1
6 33 3.0
7 33 4.0 ---------------------------------
希望得到的结果是这样的id content version
2 11 1.0
4 22 2.0
7 33 4.0
---------------------------------我想选出在各个content里 version最大的记录,我现在写的是
select content,max(version) group by version
,这样的话,不能把id 也选出来,有什么好一点的办法可以得到如上的结果,谢谢了!
这样试试
2 from (
3 select tt.*,
4 row_number() over(partition by tt.content order by tt.version desc) as rn
5 from (
6 select 1 as id,11 as content,'0.1' as version from dual
7 union all
8 select 2 as id,11 as content,'1.0' as version from dual
9 union all
10 select 3 as id,22 as content,'1.0' as version from dual
11 union all
12 select 4 as id,22 as content,'2.0' as version from dual
13 union all
14 select 5 as id,22 as content,'0.1' as version from dual
15 union all
16 select 6 as id,33 as content,'3.0' as version from dual
17 union all
18 select 7 as id,33 as content,'4.0' as version from dual
19 )tt
20 )
21 where rn = 1; ID CONTENT VERSION RN
---------- ---------- ------- ----------
2 11 1.0 1
4 22 2.0 1
7 33 4.0 1
结果是这样的
----------------------------------
id content version
2 11 1.0
5 22 2.0
7 33 4.0
可以这样试试
select a.id,a.content,a.version from T1 a,(select content,max(version) group by content) b
where a.content =b.content and a.version=b.version
现在手头上没有现成的数据库来测试
select a.id,a.content,a.version
from T1 a,(select content,max(version) version group by content) b
where a.content =b.content and a.version=b.version
select a.id,a.content,a.version
from T1 a,
(select content,max(version) version from t1 group by content) b
where a.content =b.content and a.version=b.version
from (
select tt.*,
row_number() over(partition by tt.content order by tt.version desc) as rn
from tablename
)
where rn = 1;呵呵,这样明白了吧?
from (
select tt.*,
row_number() over(partition by tt.content order by tt.version desc) as rn
from tablename tt
)
where rn = 1;
而且很喜欢做给定值的sql
看你回答的几个帖子,都是用union,都是只针对楼主贴出来的值,根本没考虑楼主贴出来的只是示范数据,所要的SQL是要适用于所有的数据,而不是贴出来的那几个数据
就像此贴
楼主贴了7行数据,你就写了6个union
如果楼主写上101行数据,你不是要写上100个union
总而言之,你是楼主写N个数据,你就写N-1个UNION
再仔细看了下,原来
select 1 as id,11 as content,'0.1' as version from dual
7 union all
8 select 2 as id,11 as content,'1.0' as version from dual
9 union all
10 select 3 as id,22 as content,'1.0' as version from dual
11 union all
12 select 4 as id,22 as content,'2.0' as version from dual
13 union all
14 select 5 as id,22 as content,'0.1' as version from dual
15 union all
16 select 6 as id,33 as content,'3.0' as version from dual
17 union all
18 select 7 as id,33 as content,'4.0' as version from dual
这段只是用来生成测试数据,前面回复时没看到后面的留言,所以想怎么弄得这么复杂
其实我觉得不需要用到partition by
一般用这个人不多,多数是用MAX来做比较的