ID LATE CLIK
1 chaia 0
2 china 0
3 china 8
4 china 1
5 china 2
6 ameirica 0
7 ameirica 1
8 ameirica 2
9 hehe 0
10 hehe 0
11 hehe 1
12 china 8
13 china 1
14 china 2
15 ameirica 1
16 ameirica 2
17 china 0
18 china 8
19 china 1
20 china 2
21 ameirica 0
22 ameirica 1
23 ameirica 2
24 hehe 0
25 hehe 0
26 hehe 1
27 china 8
28 china 1
我想要每个相同LATE里CLIK最大的那条记录,请问应该杂个实现呢,谢谢了
select * from 表名 a inner join (select late ,max(clik) from 表名 group by late) b on a.late=b.late and a.clik=b.clik
where not exists (
select 1 from tablename
where LATE=a. LATE and CLIK>a.CLIK
)
insert tab
select 1 ,'chaia', 0
union all select 2 ,'china', 0
union all select 3 ,'china', 8
union all select 4 ,'china', 1
union all select 5 ,'china', 2
union all select 6 ,'ameirica', 0
union all select 7 ,'ameirica', 1
union all select 8 ,'ameirica', 2
union all select 9 ,'hehe', 0
union all select 10 ,'hehe', 0
union all select 11 ,'hehe', 1 select * from tab a inner join (select late ,clik=max(clik) from tab group by late) b on a.late=b.late and a.clik=b.clikdrop table tab
(id char(2),name char(10),num char(2))
insert into test
select 1 as id,'china' as name, 0 as num
union all
select 2 as id,'china' as name, 1 as num
union all
select 3 as id,'china' as name, 2 as num
union all
select 4 as id,'chine' as name, 5 as num
union all
select 5 as id,'chine' as name, 5 as numselect * from test
select id
,name
,num
from test a
where num = (select max(num) from test where a.name = name)
inner join (select late ,max(clik) max_clik from t group by late)b
on a.late=b.late and a.clik=b.max_clik