表一 表2
id inqty id outqty
1 10 1 5
1 40 1 10
2 20 1 20
2 10 2 10
2 30 2 5如何通过sql语句得到如下结果:
id inqty outqty
1 10 5
1 40 10
1 20
2 20 10
2 10 5
2 30
id inqty id outqty
1 10 1 5
1 40 1 10
2 20 1 20
2 10 2 10
2 30 2 5如何通过sql语句得到如下结果:
id inqty outqty
1 10 5
1 40 10
1 20
2 20 10
2 10 5
2 30
解决方案 »
- insert语句插入第一列为固定数字怎么处理
- case when 菜鸟问题
- 加Top和不加Top有什么区别?
- 基本函数问题,如何让 1 输出为0001?再线等
- instead of触发器:怎样删除原表中与deleted表一样的记录(只有一条记录,不要说条件为所有列相等)?
- 江湖救急 SQL SERVER 2008 不能断开数据连接。
- ODBC无法连接SWLSERVER
- SQL2000中的网络备份问题!
- ~~~~~~~~~~~~~~~我使用ADO操作数据库,调用AddNew方法增加记录,如何保证增加的记录在最后面???高分.
- SQLServer中任务(JOB)问题,急!
- 求 sqlserver 书籍 pdf 免费的
- :: 啥意思
isnull(ltrim(b.outqty),'') as outqty
from a
full join b on a.id=b.id
go
create table [a]([id] int,[inqty] int)
insert [a]
select 1,10 union all
select 1,40 union all
select 2,20 union all
select 2,10 union all
select 2,30
go
if object_id('[b]') is not null drop table [b]
go
create table [b]([id] int,[outqty] int)
insert [b]
select 1,5 union all
select 1,10 union all
select 1,20 union all
select 2,10 union all
select 2,5
goselect isnull(a.id,b.id) as id,isnull(ltrim(a.inqty),'') as inqty,
isnull(ltrim(b.outqty),'') as outqty
from (select rn=row_number() over(order by getdate()),* from a) a
full join (select rn=row_number() over(order by getdate()),* from b) b on a.id=b.id and a.rn=b.rn/**
id inqty outqty
----------- ------------ ------------
1 10 5
1 40 10
1 20
2 10 10
2 30 5
2 20 (6 行受影响)
**/
go
create table [a]([id] int,[inqty] int)
insert [a]
select 1,10 union all
select 1,40 union all
select 2,20 union all
select 2,10 union all
select 2,30
go
if object_id('[b]') is not null drop table [b]
go
create table [b]([id] int,[outqty] int)
insert [b]
select 1,5 union all
select 1,10 union all
select 1,20 union all
select 2,10 union all
select 2,5
goselect isnull(a.id,b.id) as id,isnull(ltrim(a.inqty),'') as inqty,
isnull(ltrim(b.outqty),'') as outqty
from (select rn=row_number() over(order by getdate()),* from a) a
full join (select rn=row_number() over(order by getdate()),* from b) b on a.id=b.id and a.rn=b.rn/**
id inqty outqty
----------- ------------ ------------
1 10 5
1 40 10
1 20
2 10 10
2 30 5
2 20 (6 行受影响)
**/
use tempdb;
/*
create table t1
(
id int not null,
inqty int not null
);
insert into t1(id,inqty)
values
(1,10),(1,40),(2,20),(2,10),(2,30);create table t2
(
id int not null,
outqty int not null
);
insert into t2(id,outqty)
values
(1,5),(1,10),(1,20),(2,10),(2,5);
*/
select
case when temp1.id IS null then temp2.id else temp1.id end as id,
temp1.inqty,
temp2.outqty
from
(select *,ROW_NUMBER() over(partition by t1.id order by t1.id) as [num1]
from t1) as temp1
full join
(select *,ROW_NUMBER() over(partition by t2.id order by t2.id) as [num2]
from t2) as temp2
on temp1.num1 = temp2.num2 and temp1.id = temp2.id;--结果
1 10 5
1 40 10
1 NULL 20
2 20 10
2 10 5
2 30 NULL