22,300
社区成员




--> 测试数据:#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