一个表中3个字段
id,value,time
1 12 2008-10-25
2 5 2008-11-02
3 4 2008-11-05
1 6 2008-11-02
3 8 2008-11-04现在要查找每个id的最后一次写入的value,查询结果应该是id value time
1 6 2008-11-02
2 5 2008-11-02
3 4 2008-11-05SQL语句应该怎么写?
id,value,time
1 12 2008-10-25
2 5 2008-11-02
3 4 2008-11-05
1 6 2008-11-02
3 8 2008-11-04现在要查找每个id的最后一次写入的value,查询结果应该是id value time
1 6 2008-11-02
2 5 2008-11-02
3 4 2008-11-05SQL语句应该怎么写?
FROM t AS t1 INNER JOIN [select id, max(time) as mintime from t group by id]. AS t2 ON (t1.time=t2.mintime) AND (t1.id=t2.id);试试看
where t1.id=t2.id and t1.time = t2.time
我在access中试验都成功了啊
按理sqlserver应该更能通过我想是不是你程序问题,而非sql问题吧
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tab_test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tab_test]
GOCREATE TABLE [dbo].[tab_test] (
[id] [int] NOT NULL ,
[value] [int] NOT NULL ,
[time] [datetime] NOT NULL
) ON [PRIMARY]
GO运行的Sql语句:
select t1.*,t2.value from (SELECT id,max(time) as time FROM tab_test group by id) as t1,tab_test as t2 where t1.id=t2.id and t1.time = t2.time表里面的数据都是用上面的原始数据。