表结构数据如下
id date commonid value
1 2008 1 10
2 2009 1 11
3 2008 2 12
4 2008 2 13
5 2011 3 14求按照 commonid 分组 求最大date 对应的value
如果最大date相同 附加最大id条件查询结果
id date commonid value
2 2008 1 10
4 2008 2 13
5 2011 3 14只求出commonid 和value 就可以了我现在用多个in 可以实现
想要更好的更简洁的sql
谢谢了
id date commonid value
1 2008 1 10
2 2009 1 11
3 2008 2 12
4 2008 2 13
5 2011 3 14求按照 commonid 分组 求最大date 对应的value
如果最大date相同 附加最大id条件查询结果
id date commonid value
2 2008 1 10
4 2008 2 13
5 2011 3 14只求出commonid 和value 就可以了我现在用多个in 可以实现
想要更好的更简洁的sql
谢谢了
from yourTable a inner join (
select commonid,max(value) as mvalue
from yourTable
group by commonid) b
on a.commonid=b.commonid and a.value=b.mvalue;
on a.commonid=b.commonid and (a.date<b.date or (a.date=b.date and a.id<=b.id))
group by
a.id,a.date,a.commonid,a.value
having count(b.id)=1
SELECT a.id,a.date,a.commonid,a.value from ttg2 a left join ttg2 b
on a.commonid=b.commonid and a.value<=b.value
group by
a.id,a.date,a.commonid,a.value
having count(b.id)=1
select * from tb a where not exists (select * from tb where a.commonid=commonid and a.value<value);