俩个表内容并列显示，并排序，只有一个字段有关联，不是一对一的

--> 测试数据：#a

if object_id('tempdb.dbo.#a') is not null drop table #a

create table #a([user] varchar(8), name varchar(8))

insert into #a

select 'a', 'name1' union all

select 'a', 'name2' union all

select 'a', 'name3' union all

select 'b', 'name4' union all

select 'b', 'name6' union all

select 'b', 'name7' union all

select 'c', 'name8'

--> 测试数据：#b

if object_id('tempdb.dbo.#b') is not null drop table #b

create table #b([user] varchar(8), pro_name varchar(9))

insert into #b

select 'a', 'pro_name1' union all

select 'a', 'pro_name2' union all

select 'a', 'pro_name3' union all

select 'b', 'pro_name4' union all

select 'b', 'pro_name5' union all

select 'c', 'pro_name6' union all

select 'd', 'pro_name7'



select

	isnull(a.[user],b.[user])[user],

	isnull(a.name,'')name,

	isnull(b.pro_name,'')pro_name

from

	(select id=(select count(1) from #a where [user]=t.[user] and name<=t.name), * from #a t) a

full join

	(select id=(select count(1) from #b where [user]=t.[user] and pro_name<=t.pro_name), * from #b t) b

on a.[user]=b.[user] and a.id=b.id

/*

user     name     pro_name

-------- -------- ---------

a        name1    pro_name1

a        name2    pro_name2

a        name3    pro_name3

b        name4    pro_name4

b        name6    pro_name5

c        name8    pro_name6

d                 pro_name7

b        name7    

*/

CREATE TABLE #A

(

	[USER] VARCHAR(20),

	NAME VARCHAR(20)	

)

CREATE TABLE #B

(

	[USER] VARCHAR(20),

	pro_name VARCHAR(20)	

)



INSERT INTO #A SELECT 'a','name1'

INSERT INTO #A SELECT 'a','name2'

INSERT INTO #A SELECT 'a','name3'

INSERT INTO #A SELECT 'b','name4'

INSERT INTO #A SELECT 'b','name6'

INSERT INTO #A SELECT 'b','name7'

INSERT INTO #A SELECT 'c','name8'



INSERT INTO #B SELECT 'a','pro_name1'

INSERT INTO #B SELECT 'a','pro_name2'

INSERT INTO #B SELECT 'a','pro_name3'

INSERT INTO #B SELECT 'b','pro_name4'

INSERT INTO #B SELECT 'b','pro_name5'

INSERT INTO #B SELECT 'c','pro_name6'

INSERT INTO #B SELECT 'd','pro_name7'



SELECT ISNULL(M.[USER],P.[USER])[USER],

	   ISNULL(P.[NAME],'')[NAME],

	   ISNULL(M.pro_name,'')pro_name

FROM 

(

	SELECT a.*,ROW_NUMBER()OVER(PARTITION BY [USER] ORDER BY GETDATE())row

	FROM #A a

)P

FULL JOIN

(

	SELECT b.*,ROW_NUMBER()OVER(PARTITION BY [USER] ORDER BY GETDATE())row

	FROM #B b

)M

ON P.ROW=M.row AND M.[USER]=P.[USER]



USER                 NAME                 pro_name

-------------------- -------------------- --------------------

a                    name1                pro_name1

a                    name2                pro_name2

a                    name3                pro_name3

b                    name4                pro_name4

b                    name6                pro_name5

c                    name8                pro_name6

d                                         pro_name7

b                    name7                



(8 row(s) affected)

--> 测试数据：#a

if object_id('tempdb.dbo.#a') is not null drop table #a

create table #a([user] varchar(8), name varchar(8))

insert into #a

select 'a', 'name1' union all

select 'a', 'name2' union all

select 'a', 'name3' union all

select 'b', 'name4' union all

select 'b', 'name6' union all

select 'b', 'name7' union all

select 'c', 'name8'

--> 测试数据：#b

if object_id('tempdb.dbo.#b') is not null drop table #b

create table #b([user] varchar(8), pro_name varchar(9))

insert into #b

select 'a', 'pro_name1' union all

select 'a', 'pro_name2' union all

select 'a', 'pro_name3' union all

select 'b', 'pro_name4' union all

select 'b', 'pro_name5' union all

select 'c', 'pro_name6' union all

select 'd', 'pro_name7'



;with a as

(

	select id=row_number()over(partition by [user] order by name),* from #a

),

b as

(

	select id=row_number()over(partition by [user] order by pro_name),* from #b

)

select

	isnull(a.[user],b.[user])[user],

	isnull(a.name,'')name,

	isnull(b.pro_name,'')pro_name

from a full join b on a.[user]=b.[user] and a.id=b.id



/*

user     name     pro_name

-------- -------- ---------

a        name1    pro_name1

a        name2    pro_name2

a        name3    pro_name3

b        name4    pro_name4

b        name6    pro_name5

b        name7    

c        name8    pro_name6

d                 pro_name7

*/

select isnull(a.user,b.user) user,isnull(a.name,b.pro_name) name,isnull(b.pro_name,'') pro_name from a full join b on a.user=b.user