请教各位前辈:
如何按"机器序号"找出时间最近的记录?
原数据表:
日期(char(8)),机器序号, 位置
'20061010', 'AA', '东1'
'20051013', 'AA', '西2'
'20051210', 'BBB', '南1'
'20060203', 'BBB', '南2'
'20031102', 'BBB', '北1'结果:
'20061010','AA','东1'
'20060203','BBB','南2'先谢了!!!
如何按"机器序号"找出时间最近的记录?
原数据表:
日期(char(8)),机器序号, 位置
'20061010', 'AA', '东1'
'20051013', 'AA', '西2'
'20051210', 'BBB', '南1'
'20060203', 'BBB', '南2'
'20031102', 'BBB', '北1'结果:
'20061010','AA','东1'
'20060203','BBB','南2'先谢了!!!
insert @tab values(10,'am','1999-01-01')
insert @tab values(11,'am','1999-01-02') -- 相同的记录都显示
insert @tab values(11,'am','1999-01-02') -- 相同的记录都显示
insert @tab values(12,'bm','1999-01-03')
insert @tab values(13,'bm','1999-01-04')
insert @tab values(14,'cm','1999-01-05')
insert @tab values(15,'cm','1999-01-06')
--select * from @tabSELECT * FROM @tab a WHERE not exists (SELECT 1 FROM @tab b WHERE a.Column3<b.Column3 and a.Column2=b.Column2)
INSERT TEST
SELECT '20061010', 'AA', '东1' UNION ALL
SELECT '20051013', 'AA', '西2' UNION ALL
SELECT '20051210', 'BBB', '南1' UNION ALL
SELECT '20060203', 'BBB', '南2' UNION ALL
SELECT '20031102', 'BBB', '北1'
SELECT * FROM TESTSELECT * FROM TEST a WHERE not exists (SELECT 1 FROM TEST b WHERE a.日期<b.日期 and a.机器序号=b.机器序号)DROP TABLE TEST
insert @t
select '20061010', 'AA', '东1' union all
select '20051013', 'AA', '西2' union all
select '20051210', 'BBB', '南1' union all
select '20060203', 'BBB', '南2' union all
select '20031102', 'BBB', '北1'select * from @t as a
where 日期 = (select top 1 日期 from @t where 机器序号 = a.机器序号 order by 日期 desc)/*
结果:
'20061010','AA','东1'
'20060203','BBB','南2'
*/
where a.机器序号 = b.机器序号 and a.日期 = b.日期
10分钟之内就有好多的正确答案了!~推荐这个
----
SELECT * FROM TEST a WHERE not exists (SELECT 1 FROM TEST b WHERE a.日期<b.日期 and a.机器序号=b.机器序号)