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

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
...全文
107 9 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
wxl2ooo 2010-10-21
  • 打赏
  • 举报
回复
SQLCenter
很强大哦
「已注销」 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中不支持哦
「已注销」 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

22,300

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧