有表table1,其C1和C2作为组合关键字
c1 c2 c3
a 1 10
a 2 20
a 3 30
b 1 20
b 2 10
c 1 20
c 2 30
c 3 15怎么写语句查找C1重复的记录中c2为最大值的记录
比如查询上表记录后得到:
c1 c2 c3
a 3 30
b 2 10
c 3 15
c1 c2 c3
a 1 10
a 2 20
a 3 30
b 1 20
b 2 10
c 1 20
c 2 30
c 3 15怎么写语句查找C1重复的记录中c2为最大值的记录
比如查询上表记录后得到:
c1 c2 c3
a 3 30
b 2 10
c 3 15
select t.* from table1 t where t.c2=(select max(c2) from table1 where c1=t.c1)orselect t.* from table1 t where not exists(select 1 from table1 where c1=t.c1 and c2>t.c2)