现有一表:
ID Date
01 08-1-2
02 08-1-5
03 08-1-3
01 09-1-4
02 09-1-2
04 09-1-1
现根据其他条件获得一个ID List:
ID
01
03
04
需要获得此list内各ID的最后一个日期的列表,即需要此结果:
ID Date
01 09-1-4
03 08-1-3
04 09-1-1
请问该怎么写查询语句,我现在只能根据list来逐条查询,效率太低了。
谢谢指教
ID Date
01 08-1-2
02 08-1-5
03 08-1-3
01 09-1-4
02 09-1-2
04 09-1-1
现根据其他条件获得一个ID List:
ID
01
03
04
需要获得此list内各ID的最后一个日期的列表,即需要此结果:
ID Date
01 09-1-4
03 08-1-3
04 09-1-1
请问该怎么写查询语句,我现在只能根据list来逐条查询,效率太低了。
谢谢指教
A.*
FROM tb AS A
JOIN ID_List AS B
ON A.ID=B.ID
AND NOT EXISTS
(
SELECT *
FROM tb
WHERE A.ID=ID AND Date>A.Date
);
where id in (select id from ID_List)
group by ID
from ta a,
(select '01' as rid union select '03' union select '04') b
where a.id = b.rid
and not exists(select 1 from ta where id = a.id and date > a.date)
insert @t select '01','08-1-2'
insert @t select '02','08-1-5'
insert @t select '03','08-1-3'
insert @t select '01','08-1-4'
insert @t select '02','09-1-2'
insert @t select '04','09-1-1'declare @b table(id varchar(2))
insert @b select '01'
insert @b select '03'
insert @b select '04'select a.* from
(
select id,max(date) as 'date'
from @t
group by id
)a
where id in(select id from @b)
/*
id date
---- ------------------------------------------------------
01 2008-01-04 00:00:00.000
03 2008-01-03 00:00:00.000
04 2009-01-01 00:00:00.000(所影响的行数为 3 行)
*/
where id in (select id from ID_List)
group by ID
select a.* from
(
select id,max(date) as 'date'
from @t
group by id
)a
where id in(select id from @b)