二表查询

Question

/*横向转纵向*/declare @table table(name char(6),a int ,b int,c int ,d int)
insert into @table values('Tom',1,2,3,4)
insert into @table values('Jim',1,2,3,4)select name,'a' as item,a as value from @table
union all
select name,'b' as item,b as value from @table
union all
select name,'c' as item,c as value from @table
union all
select name,'d' as item,d as value from @table
order by name
/*
name   item value       
------ ---- ----------- 
Jim    a    1
Jim    b    2
Jim    c    3
Jim    d    4
Tom    d    4
Tom    c    3
Tom    b    2
Tom    a    1（所影响的行数为 8 行）
*/
drop table #temp
select *  into #temp from 
(
select name,'a' as item,a as value from @table
union all
select name,'b' as item,b as value from @table
union all
select name,'c' as item,c as value from @table
union all
select name,'d' as item,d as value from @table
) view1 
order by name,item
select * from #temp
select t0.name,t1.value as 'a',t2.value as 'c',t3.value as 'b', t4.value as 'd'
from 
(select distinct name from #temp) t0,
(select name,item ,value from #temp where item='a') t1,
(select name,item ,value  from #temp where item='c') t2,
(select name,item ,value  from #temp where item='b') t3,
(select name,item ,value  from #temp where item='d') t4
--where t0.name*=t1.name and t0.name*=t2.name and t0.name*=t3.name and t0.name*=t4.name/*纵向转横向*/select distinct  t0.name,t1.value as 'a',t2.value as 'c',t3.value as 'b', t4.value as 'd'
from 
(select distinct name from #temp) t0,
(select name,item ,value from #temp where item='a') t1,
(select name,item ,value  from #temp where item='c') t2,
(select name,item ,value  from #temp where item='b') t3,
(select name,item ,value  from #temp where item='d') t4
--where t0.name*=t1.name and t0.name*=t2.name and t0.name*=t3.name and t0.name*=t4.nameselect t0.name,t1.value as 'a',t2.value as 'c',t3.value as 'b', t4.value as 'd'
from 
(select distinct name from #temp) t0,
(select name,item ,value from #temp where item='a') t1,
(select name,item ,value  from #temp where item='c') t2,
(select name,item ,value  from #temp where item='b') t3,
(select name,item ,value  from #temp where item='d') t4
where t0.name*=t1.name and t0.name*=t2.name and t0.name*=t3.name and t0.name*=t4.nameselect name,
case when item='a' then value else 0 end as a,
case when item='c' then value else 0 end as c,
case when item='b' then value else 0 end as b,
case when item='d' then value else 0 end as d
from #temp
select name,
sum(case when item='a' then value else 0 end) as a,
sum(case when item='c' then value else 0 end) as c,
sum(case when item='b' then value else 0 end) as b,
sum(case when item='d' then value else 0 end) as d
from #temp
group by name
/*
name   a           c           b           d           
------ ----------- ----------- ----------- ----------- 
Jim    1           3           2           4
Tom    1           3           2           4（所影响的行数为 2 行）*/
附：一个通用从纵到横的简单转换存储过程
if object_id('make_fun') is not null
drop procedure make_fun
go
create procedure make_fun
(@table_to_turn varchar(255), --待旋转的表
@key_col varchar(255), --保留的关键字段
@col_know_how varchar(255), --生成列名的字段
@col_to_turn varchar(255), --作为值的字段
@how_to varchar(20)='sum') --生成值的方式 sum min max avg ,etc.
/*
过程作用，根据纵向数据生成新横向结构
by realgz@csdn 2003-12-26
*/
as
declare @exec varchar(8000)create table #tmp (col varchar(255))set @exec='select distinct '+@col_know_how+ ' from '+@table_to_turn
insert into #tmp exec (@exec)
set @exec=''select @exec=@exec+@how_to+'(case when ['+@col_know_how+']= '''+col+''' then ['+@col_to_turn +'] else null end ) as ['+col+'],'
from #tmp 
set @exec=left(@exec,len(@exec)-1)
set @exec='select ['+@key_col+'],'+@exec+' from ['+@table_to_turn+'] group by ['+@key_col+']'exec(@exec)
go

Answer 1

--sql静态语句。(假设一个员工最多只有两次合同的签定)
select m.员工Name ,
max(case n.px when 1 then n.开始日期 else null end) 第1次合同开始时间,
max(case n.px when 1 then n.结束日期 else null end) 第1次合同结束时间,
max(case n.px when 2 then n.开始日期 else null end) 第2次合同开始时间,
max(case n.px when 2 then n.结束日期 else null end) 第2次合同结束时间
from a m ,
(select * , px = (select count(1) from b where 员工ID = t.员工ID and 开始日期 < t.开始日期) + 1 from b t) n
where m.员工ID = n.员工ID
group by m.员工ID--sql动态语句。(只一个员工签定合同的次数不定)
declare @sql varchar(8000)
set @sql = 'select m.员工Name '
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then n.开始日期 else null end) [第' + cast(px as varchar) + '次合同开始时间]'
                   + ' , max(case px when ''' + cast(px as varchar) + ''' then n.结束日期 else null end) [第' + cast(px as varchar) + '次合同结束时间]'
from (select distinct px from (select * , px = (select count(1) from b where 员工ID = t.员工ID and 开始日期 < t.开始日期) + 1 from b t) o) as p
set @sql = @sql + ' from a m , ((select * , px = (select count(1) from b where 员工ID = t.员工ID and 开始日期 < t.开始日期) + 1 from b t)) n where m.员工ID = n.员工ID group by m.员工Name'
exec(@sql)