select a.*,b.date,c.num from 一张表 a left join
(select fittings_id,max(date) as date from 另一张表 group by fittings_id) b
on a.fittings_id=b.fittings_id
join 一张表 c on b.fittings_id=c.fittings_id and b.date=c.date
(select fittings_id,max(date) as date from 另一张表 group by fittings_id) b
on a.fittings_id=b.fittings_id
join 一张表 c on b.fittings_id=c.fittings_id and b.date=c.date
(select b.* from tb b inner join
(select fittings_id ,max(date) from tb group by id)c
on b.fittings_id =c.fittings_id
)d
on a.fittings_id =d.fittings_id
(select b.* from tb b inner join
(select fittings_id ,max(date) from tb group by id)c
on b.fittings_id =c.fittings_id and b.date=c.date -->加個條件
)d
on a.fittings_id =d.fittings_id
from table1 a, table2 b,
(select fittings_id,date=max(a.date) from table1 group by fittings_id)c
where a.date=c.date
(select fittings_id,date=max(date) from table2 group by fittings_id) b
on a.fittings_id=b.fittings_id
join table1 c on b.date=c.date
from table1 a, table2 b,
(select fittings_id,date=max(a.date) from table1 group by fittings_id)c
where a.date=c.date
select max(date),num from 表1 where fittings_id=表2.fittings_id)
from 表2
left join ta c on b.fittings_id = c.fittings_id and b.date_1 = c.date_1) on a.fittings_id = b.fittings_id
(select fittings_id,date=max(date) from table1 group by fittings_id) b
on a.fittings_id=b.fittings_id
join table1 c on b.fittings_id=c.fittings_id and b.date=c.datunion all
select * ,date=0,num=0 from table2 where not exists
(select fittings_id from table1 where table2.fittings_id=table1.fittings_id)
这样是对的,测试通过!
[date] varchar(8),
[num] int )
insert into ta values('1','20040502',100)
insert into ta values('1','20040602',30)
insert into ta values('1','20040702',40)
insert into ta values('2','20040502',10)
insert into ta values('2','20040602',20)
insert into ta values('3','20040702',120)
create table tb (fittings_id varchar(20),
fittings_type varchar(20),
tittings_name varchar(20))
Insert into tb values('1','1','name')
Insert into tb values('2','2','name')
Insert into tb values('3','3','name')
Insert into tb values('4','4','name')
Insert into tb values('5','5','name')select d.* ,isnull(c.[d1],'0'),isnull(c.num,0) from [tb] d left outer join
(select a.fittings_id,a.d1,b.num from (select fittings_id ,max([date]) d1 from [ta] group by fittings_id) a inner join [ta] b on
(a.[d1]=b.[date]) and (a.fittings_id=b.fittings_id)) c on
d.fittings_id=c.fittings_id
order by d.fittings_id
(select fittings_id,[date]=max([date]) from table1 group by fittings_id) b
on a.fittings_id=b.fittings_id
join table1 c on b.fittings_id=c.fittings_id and b.[date]=c.[date]union all
select * ,[date]=0,num=0 from table2 where not exists (select fittings_id from table1 where table2.fittings_id=table1.fittings_id)
select * from 表2 b left outer join
(select a.fittings_id,a.date,a.num from 表1
group by a.fittings_id,a.date,a.num)a on a.fittings_id=b.fittings_id
不过你那个0值应是表示NULL吧?
select b.fittings_id,b.fittings_type,b.fittings_name,a.dates,a.num
from s2 b left outer join
(select a.fittings_id,a.dates,a.num
from s1 a
where a.dates=(select max(dates)
from s1 c where a.fittings_id=c.fittings_id)) a
on a.fittings_id=b.fittings_ids1是个表,s2是后个表