select t1.序号, t1.名称, t1.ID , t2.百分比, t2.日期 from (select 序号,名称,max(ID) as ID from 表a group by 序号,名称) t1 inner join (select t3.* from 表b t3 where t3.日期 = (select top 1 日期 from 表b where 序号 = t3.序号 order by 日期)) t2 on t1.ID = t2.ID
select t1.序号, t1.名称, t1.ID , 百分比 = isnull(t2.百分比,0), 日期 = isnull(t2.日期 ,'2005-01-09') from (select 序号,名称,max(ID) as ID, from 表a group by 序号,名称) t1 left join 表b t2 on t1.ID = t2.ID where t2.日期 = '2005-01-09' order by t1.序号
--------上面的錯了,改 select a.*,isnull(c.百分比,0),isnull(c.日期,'2005-01-09') from a left join (select * from b where 日期='2005-01-09') c on a.序号=c.序号 and a.id=c.id where a.id='1-22'
t1.序号,
t1.名称,
t1.ID ,
t2.百分比,
t2.日期
from
(select 序号,名称,max(ID) as ID from 表a group by 序号,名称) t1
inner join
(select t3.* from 表b t3 where t3.日期 = (select top 1 日期 from 表b where 序号 = t3.序号 order by 日期)) t2
on
t1.ID = t2.ID
t1.序号,
t1.名称,
t1.ID ,
百分比 = isnull(t2.百分比,0),
日期 = isnull(t2.日期 ,'2005-01-09')
from
(select 序号,名称,max(ID) as ID, from 表a group by 序号,名称) t1
left join
表b t2
on
t1.ID = t2.ID
where
t2.日期 = '2005-01-09'
order by
t1.序号
select a.*,isnull(c.百分比,0),isnull(c.日期,'2005-01-09') from a left join
(select * from b where 日期='2005-01-09') c
on a.序号=c.序号 and a.id=c.id
where a.id='1-22'