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

wxl2ooo 2010-10-21 02:15:20
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
...全文
83 点赞 收藏 9
写回复
9 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
wxl2ooo 2010-10-21
SQLCenter
很强大哦
回复
zsh0809 2010-10-21
7#
回复
SQLCenter 2010-10-21
--> 测试数据:#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
*/
回复
wxl2ooo 2010-10-21
ROW_NUMBER() 这个函数在sqlserver2000中不支持哦
回复
zsh0809 2010-10-21
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)
回复
SQLCenter 2010-10-21
--> 测试数据:#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
*/
回复
claro 2010-10-21
貌似数据有问题?
回复
abuying 2010-10-21
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
回复
百年树人 2010-10-21
full join
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-10-21 02:15
社区公告
暂无公告