一个例子,仅供参考: ;WITH tb(dept,sh_name,sh_sex,sh_loc,sh_in_d,sh_age,sh_d,ks_name,ks_sex,ks_loc,ks_in_d,ks_age,ks_d)AS ( SELECT 'abc',NULL,NULL,NULL,NULL,NULL,NULL,'a','f','hb','2016-12-14',39,'n' UNION SELECT 'abc',NULL,NULL,NULL,NULL,NULL,NULL,'b','m','hb','2016-12-15',39,'n' UNION SELECT 'abc',NULL,NULL,NULL,NULL,NULL,NULL,'c','f','hb','2016-12-16',39,'n' UNION SELECT 'abc',NULL,NULL,NULL,NULL,NULL,NULL,'d','m','hb','2016-12-17',39,'n' UNION SELECT 'abc',NULL,NULL,NULL,NULL,NULL,NULL,'e','f','hb','2016-12-18',39,'n' UNION SELECT 'abc',NULL,NULL,NULL,NULL,NULL,NULL,'f','m','hb','2016-12-19',39,'n' UNION SELECT 'abc',NULL,NULL,NULL,NULL,NULL,NULL,'g','f','hb','2016-12-20',39,'n' UNION SELECT 'abc','aa','f','ah','2016-07-26',47,'n',NULL,NULL,NULL,NULL,NULL,NULL UNION SELECT 'abc','bb','f','ah','2016-07-25',47,'n',NULL,NULL,NULL,NULL,NULL,NULL UNION SELECT 'abc','cc','f','ah','2016-07-24',47,'n',NULL,NULL,NULL,NULL,NULL,NULL UNION SELECT 'abc','dd','f','ah','2016-07-23',47,'n',NULL,NULL,NULL,NULL,NULL,NULL ) ,m AS( SELECT *,ROW_NUMBER()OVER(PARTITION BY tb.dept ORDER BY sh_name,sh_sex,sh_loc,sh_in_d,sh_age,sh_d) AS sh_rn ,ROW_NUMBER()OVER(PARTITION BY tb.dept ORDER BY ks_name,ks_sex,ks_loc,ks_in_d,ks_age,ks_d) AS ks_rn FROM tb ) SELECT sh.dept,sh.sh_name,sh.sh_sex,sh.sh_loc,sh.sh_in_d,sh.sh_age,sh.sh_d,ks.ks_name,ks.ks_sex,ks.ks_loc,ks.ks_in_d,ks.ks_age,ks.ks_d FROM m AS sh INNER JOIN m AS ks ON sh.dept=ks.dept AND sh.sh_rn=ks.ks_rn ORDER BY sh.dept, sh.sh_rn desc dept sh_name sh_sex sh_loc sh_in_d sh_age sh_d ks_name ks_sex ks_loc ks_in_d ks_age ks_d abc dd f ah 2016-07-23 47 n g f hb 2016-12-20 39 n abc cc f ah 2016-07-24 47 n f m hb 2016-12-19 39 n abc bb f ah 2016-07-25 47 n e f hb 2016-12-18 39 n abc aa f ah 2016-07-26 47 n d m hb 2016-12-17 39 n abc NULL NULL NULL NULL NULL NULL c f hb 2016-12-16 39 n abc NULL NULL NULL NULL NULL NULL b m hb 2016-12-15 39 n abc NULL NULL NULL NULL NULL NULL a f hb 2016-12-14 39 n abc NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL abc NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL abc NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL abc NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
if object_id('tempdb..#员工表') is not null drop table #员工表 --测试数据: 男女对比 create table #员工表(部门 varchar(20),姓名 varchar(20),性别 varchar(4),到职日期 datetime,年龄 int,职称 varchar(20)) declare @i int,@b varchar(20),@m varchar(20),@x varchar(4),@d datetime,@n int,@q varchar(20) set @i=1 set @d=getdate() set @q='文员' set @x='男' while @i<=21 begin if @i<=10 set @b='板式加工1' else set @b='板式加工2' set @m='张三'+right('000'+rtrim(@i),3) set @d=@d+1 set @n=20+@i if @q='文员' set @q='职员' else set @q='文员' if @x='男' set @x='女' else set @x='男' insert into #员工表 select @b,@m,@x,@d,@n,@q set @i=@i+1 end --测试数据结束--生成报表开始 declare @左 int,@右 int declare @左表 table(部门 varchar(20),L姓名 varchar(20),L性别 varchar(4),L到职日期 datetime,L年龄 int,L职称 varchar(20),序号 int identity(1,1)) declare @右表 table(部门 varchar(20),R姓名 varchar(20),R性别 varchar(4),R到职日期 datetime,R年龄 int,R职称 varchar(20),序号 int identity(1,1))select @左=count(1) from #员工表 where 性别='男' select @右=count(1) from #员工表 where 性别='女'insert into @左表 select * from #员工表 where 性别='男' insert into @右表 select * from #员工表 where 性别='女'if @左>@右 select L.部门,L姓名,L性别,L到职日期,L年龄,L职称,R姓名,R性别,R到职日期,R年龄,R职称 from @左表 l left join @右表 r on l.序号=r.序号 else select R.部门,L姓名,L性别,L到职日期,L年龄,L职称,R姓名,R性别,R到职日期,R年龄,R职称 from @右表 r left join @左表 l on l.序号=r.序号
这种结构,在SQL SERVER中,是不推荐的。
部门,姓名,性别,到职日期,年龄,职称
合并左右2部分的数据,你的问题就不需要处理了。我的表结构是你所说的那样的,就是要形成excel形式对比效果的报表
;WITH tb(dept,sh_name,sh_sex,sh_loc,sh_in_d,sh_age,sh_d,ks_name,ks_sex,ks_loc,ks_in_d,ks_age,ks_d)AS
(
SELECT 'abc',NULL,NULL,NULL,NULL,NULL,NULL,'a','f','hb','2016-12-14',39,'n' UNION
SELECT 'abc',NULL,NULL,NULL,NULL,NULL,NULL,'b','m','hb','2016-12-15',39,'n' UNION
SELECT 'abc',NULL,NULL,NULL,NULL,NULL,NULL,'c','f','hb','2016-12-16',39,'n' UNION
SELECT 'abc',NULL,NULL,NULL,NULL,NULL,NULL,'d','m','hb','2016-12-17',39,'n' UNION
SELECT 'abc',NULL,NULL,NULL,NULL,NULL,NULL,'e','f','hb','2016-12-18',39,'n' UNION
SELECT 'abc',NULL,NULL,NULL,NULL,NULL,NULL,'f','m','hb','2016-12-19',39,'n' UNION
SELECT 'abc',NULL,NULL,NULL,NULL,NULL,NULL,'g','f','hb','2016-12-20',39,'n' UNION
SELECT 'abc','aa','f','ah','2016-07-26',47,'n',NULL,NULL,NULL,NULL,NULL,NULL UNION
SELECT 'abc','bb','f','ah','2016-07-25',47,'n',NULL,NULL,NULL,NULL,NULL,NULL UNION
SELECT 'abc','cc','f','ah','2016-07-24',47,'n',NULL,NULL,NULL,NULL,NULL,NULL UNION
SELECT 'abc','dd','f','ah','2016-07-23',47,'n',NULL,NULL,NULL,NULL,NULL,NULL
)
,m AS(
SELECT *,ROW_NUMBER()OVER(PARTITION BY tb.dept ORDER BY sh_name,sh_sex,sh_loc,sh_in_d,sh_age,sh_d) AS sh_rn
,ROW_NUMBER()OVER(PARTITION BY tb.dept ORDER BY ks_name,ks_sex,ks_loc,ks_in_d,ks_age,ks_d) AS ks_rn
FROM tb
)
SELECT sh.dept,sh.sh_name,sh.sh_sex,sh.sh_loc,sh.sh_in_d,sh.sh_age,sh.sh_d,ks.ks_name,ks.ks_sex,ks.ks_loc,ks.ks_in_d,ks.ks_age,ks.ks_d
FROM m AS sh
INNER JOIN m AS ks ON sh.dept=ks.dept AND sh.sh_rn=ks.ks_rn
ORDER BY sh.dept, sh.sh_rn desc
dept sh_name sh_sex sh_loc sh_in_d sh_age sh_d ks_name ks_sex ks_loc ks_in_d ks_age ks_d
abc dd f ah 2016-07-23 47 n g f hb 2016-12-20 39 n
abc cc f ah 2016-07-24 47 n f m hb 2016-12-19 39 n
abc bb f ah 2016-07-25 47 n e f hb 2016-12-18 39 n
abc aa f ah 2016-07-26 47 n d m hb 2016-12-17 39 n
abc NULL NULL NULL NULL NULL NULL c f hb 2016-12-16 39 n
abc NULL NULL NULL NULL NULL NULL b m hb 2016-12-15 39 n
abc NULL NULL NULL NULL NULL NULL a f hb 2016-12-14 39 n
abc NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
abc NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
abc NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
abc NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
--测试数据: 男女对比
create table #员工表(部门 varchar(20),姓名 varchar(20),性别 varchar(4),到职日期 datetime,年龄 int,职称 varchar(20))
declare @i int,@b varchar(20),@m varchar(20),@x varchar(4),@d datetime,@n int,@q varchar(20)
set @i=1
set @d=getdate()
set @q='文员'
set @x='男'
while @i<=21
begin
if @i<=10 set @b='板式加工1' else set @b='板式加工2'
set @m='张三'+right('000'+rtrim(@i),3)
set @d=@d+1
set @n=20+@i
if @q='文员' set @q='职员' else set @q='文员'
if @x='男' set @x='女' else set @x='男'
insert into #员工表 select @b,@m,@x,@d,@n,@q
set @i=@i+1
end
--测试数据结束--生成报表开始
declare @左 int,@右 int
declare @左表 table(部门 varchar(20),L姓名 varchar(20),L性别 varchar(4),L到职日期 datetime,L年龄 int,L职称 varchar(20),序号 int identity(1,1))
declare @右表 table(部门 varchar(20),R姓名 varchar(20),R性别 varchar(4),R到职日期 datetime,R年龄 int,R职称 varchar(20),序号 int identity(1,1))select @左=count(1) from #员工表 where 性别='男'
select @右=count(1) from #员工表 where 性别='女'insert into @左表 select * from #员工表 where 性别='男'
insert into @右表 select * from #员工表 where 性别='女'if @左>@右
select L.部门,L姓名,L性别,L到职日期,L年龄,L职称,R姓名,R性别,R到职日期,R年龄,R职称 from @左表 l left join @右表 r on l.序号=r.序号
else
select R.部门,L姓名,L性别,L到职日期,L年龄,L职称,R姓名,R性别,R到职日期,R年龄,R职称 from @右表 r left join @左表 l on l.序号=r.序号
查询结构就是你要的EXCL典型对比报表。