两个表:
TEST1:
A B C
F0 2009-9-1 2
F0 2009-9-2 2
F0 2009-9-4 2
F6 2009-9-1 3
TEST2:
D E F
F0 2009-9-1 4
F0 2009-9-3 4
F0 2009-9-4 4
F6 2009-9-2 5
需要算出A=D和B=E的C*F
出来结果:
F0 2009-9-1 8
F0 2009-9-2 2
F0 2009-9-3 4
F0 2009-9-4 8
F6 2009-9-1 3
F6 2009-9-2 5
TEST1:
A B C
F0 2009-9-1 2
F0 2009-9-2 2
F0 2009-9-4 2
F6 2009-9-1 3
TEST2:
D E F
F0 2009-9-1 4
F0 2009-9-3 4
F0 2009-9-4 4
F6 2009-9-2 5
需要算出A=D和B=E的C*F
出来结果:
F0 2009-9-1 8
F0 2009-9-2 2
F0 2009-9-3 4
F0 2009-9-4 8
F6 2009-9-1 3
F6 2009-9-2 5
where a.a=b.d and a.b=b.e
是这意思?
test1 inner join test2
on test1.a=test2.d and test1.b=test2.e
isnull(a,d) as ad
,isnull(b,e) as be
,isnull(c,1)*isnull(f,1) as cf
from test1 a full join test2 b
on a.a=b.d and a.b = b.e
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-15 15:14:26
-- 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.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[TEST1]
if object_id('[TEST1]') is not null drop table [TEST1]
go
create table [TEST1]([A] varchar(2),[B] datetime,[C] int)
insert [TEST1]
select 'F0','2009-9-1',2 union all
select 'F0','2009-9-2',2 union all
select 'F0','2009-9-4',2 union all
select 'F6','2009-9-1',3
--> 测试数据:[TEST2]
if object_id('[TEST2]') is not null drop table [TEST2]
go
create table [TEST2]([D] varchar(2),[E] datetime,[F] int)
insert [TEST2]
select 'F0','2009-9-1',4 union all
select 'F0','2009-9-3',4 union all
select 'F0','2009-9-4',4 union all
select 'F6','2009-9-2',5
--------------开始查询--------------------------
select
isnull(a,d) ,isnull(b,e) ,isnull(c,1)*isnull(f,1)
from
test1 a
full join test2 b on
a.a=b.d and a.b = b.e
----------------结果----------------------------
/* ---- ----------------------- -----------
F0 2009-09-01 00:00:00.000 8
F0 2009-09-02 00:00:00.000 2
F0 2009-09-04 00:00:00.000 8
F6 2009-09-01 00:00:00.000 3
F0 2009-09-03 00:00:00.000 4
F6 2009-09-02 00:00:00.000 5(6 行受影响)*/
然后对单表做乘法select A,B,C*F FROM
(
select * from TEST1 inner join TEST2
)TEMP
where A=D and B=D
不可以单纯select a.a,a.b,a.c*b.f from test1 a,test2 b where a.a=b.d and a.b=b.e
这样是得不到结果的!
insert into @Test1
select 'F0', '2009-9-1', 2
union all select 'F0', '2009-9-2', 2
union all select 'F0', '2009-9-4', 2
union all select 'F6', '2009-9-1', 3
declare @TEST2 table(D varchar(10),E datetime ,F int )
insert into @TEST2
select 'F0', '2009-9-1', 4
union all select 'F0', '2009-9-3', 4
union all select 'F0', '2009-9-4', 4
union all select 'F6', '2009-9-2', 5
select A = isnull(a.a,b.d)
,B = isnull(a.b,b.e)
,C = isnull(a.c,1)*isnull(b.f,1)
from @test1 a full join @test2 b
on a.a = b.d and a.b = b.e
order by isnull(a.a,b.d),isnull(a.b,b.e)
A B C
---------- ------------------------------------------------------ -----------
F0 2009-09-01 00:00:00.000 8
F0 2009-09-02 00:00:00.000 2
F0 2009-09-03 00:00:00.000 4
F0 2009-09-04 00:00:00.000 8
F6 2009-09-01 00:00:00.000 3
F6 2009-09-02 00:00:00.000 5(所影响的行数为 6 行)
-->Author:wufeng4552
-->Date :2009-09-15 15:16:04
if not object_id('test1') is null
drop table test1
Go
Create table test1([A] nvarchar(2),[B] Datetime,[C] int)
Insert test1
select N'F0','2009-9-1',2 union all
select N'F0','2009-9-2',2 union all
select N'F0','2009-9-4',2 union all
select N'F6','2009-9-1',3
Goif not object_id('test2') is null
drop table test2
Go
if not object_id('test2') is null
drop table test2
Go
Create table test2([D] nvarchar(2),[E] Datetime,[F] int)
Insert test2
select N'F0','2009-9-1',4 union all
select N'F0','2009-9-3',4 union all
select N'F0','2009-9-4',4 union all
select N'F6','2009-9-2',5
GoGo
select isnull(test1.a,test2.d),
isnull(test1.b,test2.e),
isnull(test1.c,1)*isnull(test2.f,1) from
test1 full join test2
on test1.a=test2.d and test1.b=test2.e
/*
---- ----------------------- -----------
F0 2009-09-01 00:00:00.000 8
F0 2009-09-02 00:00:00.000 2
F0 2009-09-04 00:00:00.000 8
F6 2009-09-01 00:00:00.000 3
F0 2009-09-03 00:00:00.000 4
F6 2009-09-02 00:00:00.000 5*/
declare @tb table([A] varchar(2),[B] datetime,[C] int)
insert @tb
select 'F0','2009-9-1',2 union all
select 'F0','2009-9-2',2 union all
select 'F0','2009-9-4',2 union all
select 'F6','2009-9-1',3
--> 测试数据:@tb2
declare @tb2 table([D] varchar(2),[E] datetime,[F] int)
insert @tb2
select 'F0','2009-9-1',4 union all
select 'F0','2009-9-3',4 union all
select 'F0','2009-9-4',4 union all
select 'F6','2009-9-2',5 select isnull(A,D),isnull( b,e), isnull(c,1)*isnull(f,1)
from @tb tb full join @tb2 tb2 on tb.a=tb2.d and b=e order by isnull(a,d),isnull( b,e)
/*
---- ----------------------- -----------
F0 2009-09-01 00:00:00.000 8
F0 2009-09-02 00:00:00.000 2
F0 2009-09-03 00:00:00.000 4
F0 2009-09-04 00:00:00.000 8
F6 2009-09-01 00:00:00.000 3
F6 2009-09-02 00:00:00.000 5(6 行受影响)
*/
test1 inner join test2
on test1.a=test2.d and test1.b=test2.e
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-15 15:14:26
-- 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.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[TEST1]
if object_id('[TEST1]') is not null drop table [TEST1]
go
create table [TEST1]([A] varchar(2),[B] datetime,[C] int)
insert [TEST1]
select 'F0','2009-9-1',2 union all
select 'F0','2009-9-2',2 union all
select 'F0','2009-9-4',2 union all
select 'F6','2009-9-1',3
--> 测试数据:[TEST2]
if object_id('[TEST2]') is not null drop table [TEST2]
go
create table [TEST2]([D] varchar(2),[E] datetime,[F] int)
insert [TEST2]
select 'F0','2009-9-1',4 union all
select 'F0','2009-9-3',4 union all
select 'F0','2009-9-4',4 union all
select 'F6','2009-9-2',5
--------------开始查询--------------------------
select
isnull(a,d) ,isnull(convert(varchar(10),b,120),convert(varchar(10),e,120)) ,isnull(c,1)*isnull(f,1)
from
test1 a
full join test2 b on
a.a=b.d and a.b = b.e
----------------结果----------------------------
/* ---- ---------- -----------
F0 2009-09-01 8
F0 2009-09-02 2
F0 2009-09-04 8
F6 2009-09-01 3
F0 2009-09-03 4
F6 2009-09-02 5(6 行受影响)
*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-15 15:14:26
-- 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.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[TEST1]
if object_id('[TEST1]') is not null drop table [TEST1]
go
create table [TEST1]([A] varchar(2),[B] datetime,[C] int)
insert [TEST1]
select 'F0','2009-9-1',2 union all
select 'F0','2009-9-2',2 union all
select 'F0','2009-9-4',2 union all
select 'F6','2009-9-1',3
--> 测试数据:[TEST2]
if object_id('[TEST2]') is not null drop table [TEST2]
go
create table [TEST2]([D] varchar(2),[E] datetime,[F] int)
insert [TEST2]
select 'F0','2009-9-1',4 union all
select 'F0','2009-9-3',4 union all
select 'F0','2009-9-4',4 union all
select 'F6','2009-9-2',5
--------------开始查询--------------------------
select
isnull(a,d) ,isnull(convert(varchar(10),b,120),convert(varchar(10),e,120)) ,isnull(c,1)*isnull(f,1)
from
test1 a
full join test2 b on
a.a=b.d and a.b = b.e
order by
isnull(a,d)
----------------结果----------------------------
/* (4 行受影响)
---- ---------- -----------
F0 2009-09-01 8
F0 2009-09-02 2
F0 2009-09-04 8
F0 2009-09-03 4
F6 2009-09-02 5
F6 2009-09-01 3(6 行受影响)*/
TEST3:
G H I
F0 2009-9-1 4
出来结果:
F0 2009-9-1 32
F0 2009-9-2 2
F0 2009-9-3 4
F0 2009-9-4 8
F6 2009-9-1 3
F6 2009-9-2 5
isnull(ad,g) as adg
,isnull(be,h) as beh
,isnull(cf,1)*isnull(i,1) as cfi
from (
select
isnull(a,d) as ad
,isnull(b,e) as be
,isnull(c,1)*isnull(f,1) as cf
from test1 a full join test2 b
on a.a=b.d and a.b = b.e
) as t
full join test3 t1 on t.ad = t1.g and t.be = t1.h