有两张表：jobs和company
jobs
uid ftitle
2 程序员
6 硬件维护
7 软件开发
2 网站策划
2 人事主管
company
uid fname
2 公司1
6 公司2
7 公司3
我现在要把两个表合并为一个表，表的结构如下
uid fname fitle1 ftitle2
2 公司1 程序员 网站策划
6 公司2 硬件维护 null
7 公司3 软件开发 null
注意：职位表里面只取出与公司表UID对应的前两条记录把它们转换成公司表的列ftitle1和ftitle2其他的省略。
数据是动态的哈。只做演示用。
------解决方案--------------------
如果只要两个
select uid,fname
,(select top 1 ftitle from jobs where uid=a.uid order by ftitle asc) as ftitle1
,(select top 1 ftitle from jobs where uid=a.uid order by ftitle desc) as ftitle2
from company a
------解决方案--------------------
or
临时表,保持次序
select IDENTITY(int,1,1) as id,* into # from jobs
select uid,fname
,(select ftitle from # t where uid=a.uid and not exists (
select 1 from # where uid=a.uid and id <t.id
)
) as ftitle1
,(select ftitle from jobs where uid=a.uid and (
select count(*) from # where uid=a.uid and id <t.id
)=1
) as ftitle2
from company a
------解决方案--------------------
--创建测试环境
drop table jobs,company
go
create table jobs(id int identity(1,1),uid int,ftitle varchar(20))
insert into jobs(uid,ftitle)
select 2, '程序员 '
union all select 6, '硬件维护 '
union all select 7, '软件开发 '
union all select 2, '网站策划 '
union all select 2, '人事主管 '
create table company(uid int,fname varchar(20))
insert into company
select 2, '公司1 '
union all select 6, '公司2 '
union all select 7, '公司3 '
--查询
select a.uid,
a.fname,
id1=(select top 1 id from jobs b where b.uid=a.uid order by b.ftitle),
fitle1=(select top 1 ftitle from jobs b where b.uid=a.uid order by b.ftitle),
id2=(select top 1 id from jobs c where c.uid=a.uid and c.ftitle <> (select top 1 ftitle from jobs b where b.uid=a.uid order by b.ftitle)),
fitle2=(select top 1 ftitle from jobs c where c.uid=a.uid and c.ftitle <> (select top 1 ftitle from jobs b where b.uid=a.uid order by b.ftitle))
from company a
/*
uid fname id1 fitle1 id2 fitle2
----------- -------------------- ----------- -------------------- ----------- --------------------
2 公司1 1 程序员 4 网站策划
6 公司2 2 硬件维护 NULL NULL
7 公司3 3 软件开发 NULL NULL
（所影响的行数为 3 行）
*/
------解决方案--------------------
select a.uid,
a.fname,
ftitle1=(select top 1 ftitle from jobs b where b.uid=a.uid order by b.ftitle),
ftitle2=(select top 1 ftitle from jobs c where c.uid=a.uid and c.ftitle not in(select top 1 ftitle from jobs b where b.uid=a.uid order by b.ftitle)order by c.ftitle),