34,588
社区成员
发帖
与我相关
我的任务
分享
--------------------SQL Server数据格式化工具-------------------
---------------------------------------------------------------
-- DESIGNER :happycell188(喜喜)
-- QQ :584738179
-- Development Tool :Microsoft Visual C++ 6.0 C Language
-- FUNCTION :CONVERT DATA TO T-SQL
---------------------------------------------------------------
-- Microsoft SQL Server 2005
-- Developer Edition on Microsoft Windows XP [版本 5.1.2600]
---------------------------------------------------------------
---------------------------------------------------------------
use test
go
if object_id('test.dbo.tb1') is not null drop table tb1
-- 创建数据表
create table tb1
(
物料 char(2),
庫存 int,
倉庫 int
)
go
--插入测试数据
insert into tb1 select 'A',30,1
union all select 'A',50,2
union all select 'B',20,2
go
if object_id('test.dbo.tb2') is not null drop table tb2
-- 创建数据表
create table tb2
(
物料 char(2),
欠數 int,
倉庫 int
)
go
--插入测试数据
insert into tb2 select 'A',20,1
union all select 'C',20,2
go
--代码实现
select t3.物料,庫存=isnull(t1.庫存,0),欠數=isnull(t2.欠數,0),t3.倉庫
from (select distinct * from (
select 物料,倉庫 from tb1
union all select 物料,倉庫 from tb2 )t
)t3 left join tb1 t1 on t1.物料=t3.物料 and t1.倉庫=t3.倉庫
left join tb2 t2 on t2.物料=t3.物料 and t2.倉庫=t3.倉庫
/*测试结果
物料 庫存 欠數 倉庫
----------------------------------
A 30 20 1
A 50 0 2
B 20 0 2
C 0 20 2
(4 行受影响)
*/
Select IsNull(a.[物料],b.[物料]) as 物料,
IsNull(a.[庫存],0) [庫存],
IsNull(b.[欠數],0) [欠數],
IsNull(a.[倉庫],b.[倉庫]) as [倉庫]
from
a full join b on a.[物料]=b.[物料] and a.[倉庫]=b.[倉庫]
物料 庫存 欠數 倉庫
---- ----------- ----------- -----------
A 30 20 1
A 50 0 2
B 20 0 2
C 0 20 2
(所影响的行数为 4 行)
Select IsNull(t1.[物料],t2.[物料]) as 物料,
IsNull(t1.[库存],0) [库存],
IsNull(t2.[欠數],0) [欠數],
isnull(IsNull(t1.[仓库],t2.[仓库]) as [仓库]
from t1
full join t2 on t1.[物料]=t2.[物料] and t1.[仓库]=t2.[仓库]
select isnull(表一.物料, 表二.物料) 物料, isnull(庫存, 0) 庫存, isnull(欠數, 0) 欠數, isnull(表一.倉庫, 表二.倉庫)
from 表一 full join 表二 on 表一.物料 = 表二.物料 and 表一.倉庫 = 表二.倉庫
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-05-01 18:46:37
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([物料] varchar(1),[庫存] int,[倉庫] int)
insert [a]
select 'A',30,1 union all
select 'A',50,2 union all
select 'B',20,2
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([物料] varchar(1),[欠數] int,[倉庫] int)
insert [b]
select 'A',20,1 union all
select 'C',20,2
--------------开始查询--------------------------
select
isnull(a.物料,b.物料) as 物料,isnull(a.庫存,0) as 庫存,isnull(b.欠數,0) as 欠數,isnull(a.倉庫,b.倉庫) as 倉庫
from
a full join b
on
a.物料=b.物料
and
a.倉庫=b.倉庫
----------------结果----------------------------
/* 物料 庫存 欠數 倉庫
---- ----------- ----------- -----------
A 30 20 1
A 50 0 2
B 20 0 2
C 0 20 2
(4 行受影响)
*/