有两张表:jobs和companyjobs
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其他的省略。数据是动态的哈。只做演示用。
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
临时表,保持次序select IDENTITY(int,1,1) as id,* into # from jobsselect 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(uid int,ftitle varchar(20))
insert into jobs
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,
fitle1=(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 fitle1 fitle2
----------- -------------------- -------------------- --------------------
2 公司1 程序员 网站策划
6 公司2 硬件维护 NULL
7 公司3 软件开发 NULL(所影响的行数为 3 行)
*/
6 公司2 硬件维护 硬件维护
7 公司3 软件开发 软件开发
跟我的要求不一样啊。
2 公司1 程序员 网站策划
6 公司2 硬件维护 null
7 公司3 软件开发 null
若加两个字段ID1和ID2怎么搞。
ID1倒是挺好加的,
ID2怎么加呢。
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 行)
*/
drop table jobs,company
go
create table jobs(uid int,ftitle varchar(20))
insert into jobs
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,
fitle1=(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
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),
ftitle3= (select top 1 ftitle from jobs d where d.uid=a.uid and d.ftitle not in(select top 2 ftitle from jobs b where b.uid=a.uid order by b.ftitle)order by d.ftitle)
from company a
查询结果uid fname ftitle1 ftitle2 ftitle3
2 公司1 程序员 人事主管 网站策划
6 公司2 硬件维护 NULL NULL
7 公司3 软件开发 NULL NULL按中文升序排的,你可以更改排序顺序来去掉你不需要的如果需要你给的这个结果,可以写成
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 desc)
from company a
查询结果uid fname fitle1 ftitle2
2 公司1 程序员 网站策划
6 公司2 硬件维护 null
7 公司3 软件开发 null
gahade(与君共勉) 写的就可以了
---借楼上的楼上的数据
--创建测试环境
if object_id('jobs') is not null
drop table jobs
go
if object_id('company') is not null
drop table company
go
create table jobs(uid int,ftitle varchar(20))
insert into jobs
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'
go
--增加id辅助列
alter table jobs add id int
go
declare @id int
set @id=0
update jobs set id=@id,@id=@id+1
go
update a set
id=(select count(1) from jobs where uid=a.uid and id<=a.id)
from jobs a
--查看增加辅助列后的测试数据
select * from jobs
select * from companydeclare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case id when '''+cast(id as varchar)+''' then ftitle else null end)
as [ftitle'+cast(id as varchar)+']' from jobs group by id
exec('select a.uid,a.fname'+@sql+'from jobs b inner join company a on a.uid=b.uid group by a.uid,a.fname')
uid fname ftitle1 ftitle2 ftitle3
----------- -------------------- -------------------- -------------------- --------------------
2 公司1 程序员 网站策划 人事主管
6 公司2 硬件维护 NULL NULL
7 公司3 软件开发 NULL NULL
*/