一人Usr表,一个工作经历表Job
Usr
ID Name
1 mai
2 yuang
JobRecords
ID UserId Company StartDate
1 1 a 2002-02-02
2 1 b 2002-05-02
3 2 a 2002-02-02
4 2 d 2002-03-02
5 2 e 2002-04-02怎样查找显示以下的结果,不用临时表和游标Name Company StartDate
mai b 2002-05-02
yuang e 2002-04-02
雨夜花上飞(1461373) 17:43:30
一人Usr表,一个工作经历表JobRecords
Usr
ID Name
1 mai
2 yuang
JobRecords
ID UserId Company StartDate
1 1 a 2002-02-02
2 1 b 2002-05-02
3 2 a 2002-02-02
4 2 d 2002-03-02
5 2 e 2002-04-02怎样查找显示以下的结果,不用临时表和游标Name Company StartDate
mai b 2002-05-02
yuang e 2002-04-02
Usr
ID Name
1 mai
2 yuang
JobRecords
ID UserId Company StartDate
1 1 a 2002-02-02
2 1 b 2002-05-02
3 2 a 2002-02-02
4 2 d 2002-03-02
5 2 e 2002-04-02怎样查找显示以下的结果,不用临时表和游标Name Company StartDate
mai b 2002-05-02
yuang e 2002-04-02
雨夜花上飞(1461373) 17:43:30
一人Usr表,一个工作经历表JobRecords
Usr
ID Name
1 mai
2 yuang
JobRecords
ID UserId Company StartDate
1 1 a 2002-02-02
2 1 b 2002-05-02
3 2 a 2002-02-02
4 2 d 2002-03-02
5 2 e 2002-04-02怎样查找显示以下的结果,不用临时表和游标Name Company StartDate
mai b 2002-05-02
yuang e 2002-04-02
(
select * from JobRecords a
where not exists(select 1 from JobRecords where userid=a.userid and startdate>a.startdate)
) a inner join Usr b
on a.userid=b.id
insert into @T select 1,'mai'
union all select 2,'yuang'declare @a table(ID int,UserId int,Company varchar(10),StartDate varchar(10))
insert into @a select 1,1,'a','2002-02-02'
union all select 2,1,'b','2002-05-02'
union all select 3,2,'a','2002-02-02'
union all select 4,2,'d','2002-03-02'
union all select 5,2,'e','2002-04-02'select Name,Company,StartDate from @t a,(select * from @a a where not exists(select 1 from @a where UserId=a.UserId and StartDate>a.StartDate))b
where a.id=b.UserId
A.Name,
B.Company,
B.StartDate
From Usr A
Inner Join (Select * From JobRecords B Where StartDate=(Select Max(StartDate) From JobRecords Where UserId=B.UserId)) C
On A.ID=C.UserId
from JobRecords A
where ID in
(
select max(id)
from JobRecords
group by UserID
)
insert @Usr
select
1, 'mai' union all
select
2, 'yuang'
declare @JobRecords table(id int,UserId int,Company char(5),StartDate varchar(50))
insert @JobRecords
select
1, 1, 'a', '2002-02-02'union all select
2, 1, 'b', '2002-05-02'union all select
3, 2, 'a', '2002-02-02'union all select
4, 2, 'd', '2002-03-02'union all select
5, 2, 'e', '2002-04-02'
select a.name,d.Company,d.StartDate from @Usr a,
(
select b.* from @JobRecords b,
(select UserId,max(StartDate)as startdate from @JobRecords group by UserId)c
where b.UserId=c.UserId and b.startdate=c.startdate
)d
where a.id=d.UserId
Usr
ID Name
1 mai
2 yuang
3 changJobRecords
ID UserId Company StartDate
1 1 a 2002-02-02
2 1 b 2002-05-02
3 2 a 2002-02-02
4 2 d 2002-03-02
5 2 e 2002-04-02怎样查找显示以下的结果,不用临时表和游标,取StartDate最大值,如果没有工作经历,则显示为空.
Name Company StartDate
mai b 2002-05-02
yuang e 2002-04-02
chang NULL NULL
A.Name,
B.Company,
B.StartDate
From Usr A
Left Join (Select * From JobRecords B Where StartDate=(Select Max(StartDate) From JobRecords Where UserId=B.UserId)) C
On A.ID=C.UserId
A.Name,
C.Company,
C.StartDate
From Usr A
Left Join (Select * From JobRecords B Where StartDate=(Select Max(StartDate) From JobRecords Where UserId=B.UserId)) C
On A.ID=C.UserId