现在表是这样的结构
id date num1
id1 2008-9-1 12
id1 2008-9-2 23
id1 2008-9-3 22
id1 2008-9-4 33
id2 2008-9-2 32
id2 2008-9-3 12
id2 2008-9-4 22
id3 2008-9-1 22
id3 2008-9-2 12
id3 2008-9-3 32
id3 2008-9-4 12
id3 2008-9-5 22
id4 2008-9-5 52
id4 2008-9-6 22
id4 2008-9-7 12
id4 2008-9-8 32id和date列是个联合主键,每个id对应保存大于3天的数据,现在我想查询每个id分组中最近3天num1的最大值是多少
希望查出来的结果如下:
id date num1
id1 2008-9-4 33
id2 2008-9-2 32
id3 2008-9-3 32
id4 2008-9-8 32希望各路大侠,高手帮帮忙,谢谢
id date num1
id1 2008-9-1 12
id1 2008-9-2 23
id1 2008-9-3 22
id1 2008-9-4 33
id2 2008-9-2 32
id2 2008-9-3 12
id2 2008-9-4 22
id3 2008-9-1 22
id3 2008-9-2 12
id3 2008-9-3 32
id3 2008-9-4 12
id3 2008-9-5 22
id4 2008-9-5 52
id4 2008-9-6 22
id4 2008-9-7 12
id4 2008-9-8 32id和date列是个联合主键,每个id对应保存大于3天的数据,现在我想查询每个id分组中最近3天num1的最大值是多少
希望查出来的结果如下:
id date num1
id1 2008-9-4 33
id2 2008-9-2 32
id3 2008-9-3 32
id4 2008-9-8 32希望各路大侠,高手帮帮忙,谢谢
from yourTable a inner join (select id,max(num1) as mnum1 from yourTable group by id where `date`<CURDATE()-3) b
on a.id=b.id and a.num1=b.mnum1
where `date`<CURDATE()-3[/code]
from yourTable a inner join (select id,max(num1) as mnum1 from yourTable group by id where `date`>CURDATE()-3) b
on a.id=b.id and a.num1=b.mnum1
where `date`>CURDATE()-3;
select a.*
from yourTable a
where `date`>CURDATE()-3
and not exists (select id from yourTable where `date`>CURDATE()-3 and id=a.id and num1>a.num1);
from tb
where to_days(now())-to_days(date)<=3
group by id
*
from
tb as tt
where
num1 in(select max(num1)
from tb as t
where (select count(1) from tb where id=t.id and date>=t.date)<=3
and id=tt.id
)
id4 2008-9-5 52
id4 2008-9-6 22
id4 2008-9-7 12
id4 2008-9-8 32 最近3天最大的记录,9-5号已经不是最近3天了,谢谢
4楼应该可以的,没有mysql环境测试,在mssql下测试了一下,是可以的
select a.* from (
SELECT A.ID,A.DATE,A.NUM1
FROM TTH1 A LEFT JOIN TTH1 B ON A.ID=B.ID AND A.DATE<=B.DATE GROUP BY A.ID,A.DATE,A.NUM1
HAVING COUNT(*)<=3) a
inner join
(select id,max(num1) as ma from
(
SELECT A.ID,A.DATE,A.NUM1
FROM TTH1 A LEFT JOIN TTH1 B ON A.ID=B.ID AND A.DATE<=B.DATE GROUP BY A.ID,A.DATE,A.NUM1
HAVING COUNT(*)<=3) a group by id) c
on a.id=c.id and a.num1=c.ma