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

[wxl2ooo]

A表：
user name

a name1
a name2
a name3
b name4
b name6
b name7
c name8

B 表

user pro_name

a pro_name1
a pro_name2
a pro_name3
b pro_name4
b pro_name5
c pro_name6
d pro_name7

想要的结果

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

[百年树人]

full join

[abuying]

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

[claro]

貌似数据有问题？

[SQLCenter]

--> 测试数据：#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
*/

[「已注销」]

TRY~~

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)

[wxl2ooo]

ROW_NUMBER() 这个函数在sqlserver2000中不支持哦

[SQLCenter]

--> 测试数据：#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    
*/

[「已注销」]

7#

[wxl2ooo]

SQLCenter
很强大哦