表结构及其数据如下:
name mytime weight
--------------------------------
alice 2009-03-09 48
bob 2009-03-09 14
jack 2009-02-01 50
jack 2009-02-01 58我想选取以日期排序后每日最大的纪录,例如上述数据对应的结果是:
alice 2009-03-09 48
jack 2009-02-01 58
请教SQL语句怎么写?
name mytime weight
--------------------------------
alice 2009-03-09 48
bob 2009-03-09 14
jack 2009-02-01 50
jack 2009-02-01 58我想选取以日期排序后每日最大的纪录,例如上述数据对应的结果是:
alice 2009-03-09 48
jack 2009-02-01 58
请教SQL语句怎么写?
(select mytime,max(weight) as ma from tt group by mytime) b
on a.mytime=b.mytime and a.weight=b.ma
select a.name,a.mytime,a.weight from tt a
left join tt b on a.mytime=b.mytime and a.weight<=b.weight
group by a.name,a.mytime,a.weight
having count(b.name)=1
from yourTable a inner join (
select name,max(weight) as mweight
from yourTable
group by name
) b
on a.name=b.name and a.weight=b.mweight
from yourTable a
where not exists (select 1 from yourTable where name=a.name and weight>a.weight)
select *
from tb t
where not exists(select * from tb where name=t.name and weight>t.weight)
我还要实现,如果同一天的结果数量大于1,则只选择一条。。例如
表结构及其数据如下:
name mytime weight other
--------------------------------
alice 2009-03-09 48 8
alice 2009-03-09 48 9
jack 2009-02-01 50 1
jack 2009-02-01 58 2可以看到如果选择出来的话,找alice的话,结果是
name mytime weight other
--------------------------------
alice 2009-03-09 48 8
alice 2009-03-09 48 9可是我只想要other值比较大的。。
(select mytime,max(weight) as ma1,MAX(other) as ma1 from tt group by mytime) b
on a.mytime=b.mytime and a.weight=b.ma AND A.OTHER=B.ma1
select a.name,a.mytime,a.weight from tt a
left join tt b on a.mytime=b.mytime and a.weight <=b.weight
AND A.OTHER<=B.OTHER
group by a.name,a.mytime,a.weight
having count(b.name)=1
name mytime weight other
--------------------------------
alice 2009-03-09 48 4
alice 2009-03-09 48 4
jack 2009-02-01 50
jack 2009-02-01 5这时候查询alice就有两条啊..
我就要一条呢?
--------------------------------
alice 2009-03-09 48 4 myself
alice 2009-03-09 48 4 your
jack 2009-02-01 50 2 ok
jack 2009-02-01 5 1 ok选择时候如果重复了,那么就选取长度大的,例如上述是myself那条。。
如果长度一样,那就比较字符串大小,选择大的。在咋整呢。。拜谢啊
长度优先,字符串大小次之。。
from yourTable a
where not exists (
select 1 from yourTable
where name=a.name
and (
weight>a.weight
or (weight=a.weight and other>a.other)
or (weight=a.weight and other=a.other and len(word)>len(a.word))
or (weight=a.weight and other=a.other and len(word)=len(a.word) and word>a.word)
)
)
left join tt1 b on a.DTime=b.DTime and a.wGT <=b.WGT
AND (LEN(A.WORD)<=LEN(B.WORD) OR (A.WORD<=B.WORD))
group by a.name,a.DTime,a.WORD,A.WGT
select a.name,a.DTime,a.WORD,A.WGT from tt1 a
left join tt1 b on a.DTime=b.DTime and a.wGT <=b.WGT
AND (LEN(A.WORD)<=LEN(B.WORD) AND (A.WORD<=B.WORD))
group by a.name,a.DTime,a.WORD,A.WGT
HAVING COUNT(B.WORD)<=2
left join tt1 b on a.DTime=b.DTime and a.wGT <=b.WGT
AND (LENgth(A.WORD) <=LENgth(B.WORD) AND (A.WORD <=B.WORD))
group by a.name,a.DTime,a.WORD,A.WGT
HAVING COUNT(B.WORD) <=2
已经测试通过