34,590
社区成员
发帖
与我相关
我的任务
分享
select
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
----------------------------------------------------------------
-- 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 行受影响)
*/
---改整齐点
----------------------------------------------------------------
-- 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 行受影响)
*/
--> 测试数据:@tb
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 行受影响)
*/
-->Title:生成測試數據
-->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
Go
if 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
Go
Go
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 @TEST1 table(A varchar(10),B datetime ,C int )
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 行)
select A,B,C*F FROM
(
select * from TEST1 inner join TEST2
)TEMP
where A=D and B=D
---同意海爷意见
----------------------------------------------------------------
-- 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
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
select test1.a,test1.b,test1.c*test2.f from
test1 inner join test2
on test1.a=test2.d and test1.b=test2.e
select A,C*F from test1,test2 where A=D and B=E