Select t1.id,t1.name,sum(t2.code) as Code
from table1 as t1 Left join table2 as t2
on t1.id=t2.id
group by t1.id,t1.name
order by t1.id
from table1 as t1 Left join table2 as t2
on t1.id=t2.id
group by t1.id,t1.name
order by t1.id
(select max(datetime) from table2 where id=a.b.id)
Select t1.id,t1.name,sum(t2.code) as Code
from table1 as t1 Left join
(Select id,max(datetime) as datetime from table2
group by id) as t2 on t1.id=t2.id
inner join table2 on table2.id=t2.id and table2.datetime=t2.datetime
group by t1.id,t1.name
order by t1.id
id varchar(20),
name varchar(20)
)insert @a
select
'001', 'aa'
union all select
'002', 'bb'
union all select
'003', 'cc'declare @b table (
id varchar(20),
code varchar(20),
[datetime] datetime
)
insert @b
select
'001', '12', '2004-4-4'
union all select
'001' , '56' , '2004-2-3'
union all select
'001' , '84' , '2004-5-6'
union all select
'002' , '12' , '2004-1-2'
union all select
'002' , '89' , '2004-7-8'select a.*,b.code from @a a left join @b b
on a.id=b.id and b.[datetime]=
(select max([datetime]) from @b where id=b.id)
/*
id name code
-------------------- -------------------- --------------------
001 aa 84
002 bb 89
003 cc NULL(所影响的行数为 3 行)
*/
只是将table1中在table2中有对应值的检索出来了,但是,table1中的记录没有全部显示。
table1中的记录要全部显示啊。
请大家赶快帮帮我吧
谢谢
declare @a table (
id varchar(20),
name varchar(20)
)insert @a
select
'001', 'aa'
union all select
'002', 'bb'
union all select
'003', 'cc'declare @b table (
id varchar(20),
code varchar(20),
[datetime] datetime
)
insert @b
select
'001', '12', '2004-4-4'
union all select
'001' , '56' , '2004-2-3'
union all select
'001' , '84' , '2004-5-6'
union all select
'002' , '12' , '2004-1-2'
union all select
'002' , '89' , '2004-7-8'select a.*,isnull((select top 1 code from @b where id=a.id order by [datetime] desc),'-') as code from @a a /*
结果
id name code
-------------------- -------------------- --------------------
001 aa 84
002 bb 89
003 cc -(所影响的行数为 3 行)
*/
(select max(datetime) from table2 where id=a.id)
union all select
'001' , '56' , '2004-2-3'
union all select
'001' , '84' , '2004-5-6'
union all select
'002' , '12' , '2004-1-2'
union all select
'002' , '89' , '2004-7-8'上面的是什么意思?我的记录不止这几个
from table1 a
left join(
select id,[datetime]=max([datetime])
from table2
group by id
)bb on a.id=bb.id
left join table2 b on b.id=bb.id and b.[datetime]=bb.[datetime]
declare @a table (id varchar(20),name varchar(20))
insert @a select '001','aa'
union all select '002','bb'
union all select '003','cc'declare @b table (id varchar(20),code varchar(20),[datetime] datetime)
insert @b select '001','12','2004-4-4'
union all select '001','56','2004-2-3'
union all select '001','84','2004-5-6'
union all select '002','12','2004-1-2'
union all select '002','89','2004-7-8'--查询
select a.*,b.*
from @a a
left join(
select id,[datetime]=max([datetime])
from @b
group by id
)bb on a.id=bb.id
left join @b b on b.id=bb.id and b.[datetime]=bb.[datetime]/*--测试结果id name id code datetime
------ ------ ------ ------ -----------------------
001 aa 001 84 2004-05-06 00:00:00.000
002 bb 002 89 2004-07-08 00:00:00.000
003 cc NULL NULL NULL(所影响的行数为 3 行)
--*/
@a a left join @b b
on a.id=b.id and b.datetime=
(select max(datetime) from @b where id=b.id) --接上面的,也可以得到结果