A表:
user name
a name1
a name2
a name3
b name4
b name6
b name7
c name8B 表
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
user name
a name1
a name2
a name3
b name4
b name6
b name7
c name8B 表
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
解决方案 »
- sql server 2000 数据类型转换
- 求好使的SQL导出EXECL2007命令
- sql2000在window2000下安装指定的服务并未以已安装的服务存在。
- 如何把本地的存储过程放到服务器上,我是指一次性的,不要单独的一个一个放或是备份的方式放
- 有没有一个存储过程得到当前表的所有插入表的记录,就是inserted里面的值,但还是没有提交
- 库存管理中,物料发出成本计算及库存计算方法请教(SQL)
- 如何初始化一个信息管理系统的数据库?在系统安装时实现
- 关于数据库设计的,避免数据重复!
- 一个sqlserver7.0和delphi编程问题请教
- Sql Server2005默认的update是 表锁还是行级锁
- 分时间段统计
- 求一个分类统计
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
*/
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)
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
*/