有两个表 A 和B
A表id name
-------------
1 a
2 a
3 a
4 b
5 b
6 b
B表id date
-------------
1 2006-05-09
2 2006-09-23
3 2006-10-03
4 2006-09-11
5 2006-05-06
6 2006-12-09最后要的结果是关连两个表 取得名称相同中时间最接近现在的一条记录想要的结果 C 表:id name date
-------------------------
1 a 2006-10-03
2 b 2006-12-09这该怎么写啊?想的头都 大了
麻烦各位了!!!
A表id name
-------------
1 a
2 a
3 a
4 b
5 b
6 b
B表id date
-------------
1 2006-05-09
2 2006-09-23
3 2006-10-03
4 2006-09-11
5 2006-05-06
6 2006-12-09最后要的结果是关连两个表 取得名称相同中时间最接近现在的一条记录想要的结果 C 表:id name date
-------------------------
1 a 2006-10-03
2 b 2006-12-09这该怎么写啊?想的头都 大了
麻烦各位了!!!
create table #A (id int,name varchar(100))
insert into #A select 1,'a'
insert into #A select 2,'a'
insert into #A select 3,'a'
insert into #A select 4,'b'
insert into #A select 5,'b'
insert into #A select 6,'b'
create table #B(id int,[date] datetime)
insert into #B select 1,'2006-05-09'
insert into #B select 2,'2006-09-23'
insert into #B select 3,'2006-10-03'
insert into #B select 4,'2006-09-11'
insert into #B select 5,'2006-05-06'
insert into #B select 6,'2006-12-09'
select min(t1.id),t1.name,max(t2.[date])
from #A t1 inner join #B t2 on t1.id=t2.id
group by t1.namedrop table #A,#B
select * from temptb
其中temptb是一个临时表
这样就可以实现LZ的功能
这是怎么回事?
from
(select min(t1.id),t1.name,max(t2.[date])
from #A t1 inner join #B t2 on t1.id=t2.id
group by t1.name) T inner join #B on T.id=#B.id
create table #A (id int,name varchar(100))
insert into #A select 1,'a'
insert into #A select 2,'a'
insert into #A select 3,'a'
insert into #A select 4,'b'
insert into #A select 5,'b'
insert into #A select 6,'b'
create table #B(id int,[date] datetime,其他字段名 int)
insert into #B select 1,'2006-05-09',1
insert into #B select 2,'2006-09-23',3
insert into #B select 3,'2006-10-03',4
insert into #B select 4,'2006-09-11',5
insert into #B select 5,'2006-05-06',8
insert into #B select 6,'2006-12-09',9
select min(t1.id) as id,t1.name,max(t2.[date]) as [date]
from #A t1 inner join #B t2 on t1.id=t2.id
group by t1.name--显示其他字段
select T.*,#B.其他字段名
from
(select min(t1.id) as id,t1.name,max(t2.[date]) as [date]
from #A t1 inner join #B t2 on t1.id=t2.id
group by t1.name) T inner join #B on T.id=#B.id
drop table #A,#B
create table #A (id int,name varchar(100))
insert into #A select 1,'a'
insert into #A select 2,'a'
insert into #A select 3,'a'
insert into #A select 4,'b'
insert into #A select 5,'b'
insert into #A select 6,'b'
create table #B(id int,[date] datetime,其他字段名 int)
insert into #B select 1,'2006-05-09',1
insert into #B select 2,'2006-09-23',3
insert into #B select 3,'2006-10-03',4
insert into #B select 4,'2006-09-11',5
insert into #B select 5,'2006-05-06',8
insert into #B select 6,'2006-12-09',9
select min(t1.id) as id,t1.name,max(t2.[date]) as [date]
from #A t1 inner join #B t2 on t1.id=t2.id
group by t1.name--显示其他字段
select T.*,#B.其他字段名
from
(select min(t1.id) as id,t1.name,max(t2.[date]) as [date]
from #A t1 inner join #B t2 on t1.id=t2.id
group by t1.name) T inner join #B on T.id=#B.id
drop table #A,#B