Select name From C01..sysobjects Where type = 'U' Order By name
Select name From C02..sysobjects Where type = 'U' Order By name
Select name From C03..sysobjects Where type = 'U' Order By name将这三个查询(查询用户表)排成三列SQL怎么写?name1 name2 name3
CUST CUST CUST
ADD1 ADD1 ADD1
ADD2 ADD2 null
AC01 AC01 AC01
AC02 null AC02
AC03 null AC03
null null AC04参考上贴:http://topic.csdn.net/u/20090410/09/808821c0-5efb-40cc-92a4-ae371b1da986.html
Select name From C02..sysobjects Where type = 'U' Order By name
Select name From C03..sysobjects Where type = 'U' Order By name将这三个查询(查询用户表)排成三列SQL怎么写?name1 name2 name3
CUST CUST CUST
ADD1 ADD1 ADD1
ADD2 ADD2 null
AC01 AC01 AC01
AC02 null AC02
AC03 null AC03
null null AC04参考上贴:http://topic.csdn.net/u/20090410/09/808821c0-5efb-40cc-92a4-ae371b1da986.html
t1.name as name1,
t2.name as name2,
t3.name as name3
from
(Select name From C01..sysobjects Where type = 'u' ) t1
full join
(Select name From C02..sysobjects Where type = 'u' ) t2
full join
(Select name From C03..sysobjects Where type = 'u' ) t3
on
t1.name=t2.name and t2.name=t3.name and t1.name=t3.name
t1.name as name1,
t2.name as name2,
t3.name as name3
from
(Select name From C01..sysobjects Where type = 'u' ) t1
full join
(Select name From C02..sysobjects Where type = 'u' ) t2
on
t1.name=t2.name
full join
(Select name From C03..sysobjects Where type = 'u' ) t3
on
t2.name=t3.name and t1.name=t3.name
//----------------------------
谢谢!这样通不过(SQLServer 2000)!
t1.name as name1,
t2.name as name2,
t3.name as name3
from
(Select name From C01..sysobjects Where type = 'u' ) t1
full join
(Select name From C02..sysobjects Where type = 'u' ) t2
on
t1.name=t2.name
full join
(Select name From C03..sysobjects Where type = 'u' ) t3
on
t2.name=t3.name and t1.name=t3.name谢谢!已经到了这一步,离结果还差一点点:
GITM GITM GITM
MINQ NULL NULL
NACAC01 NULL NULL
NACDC01 NULL NULL
NACEC01 NULL NULL
NACGC01 NULL NULL
OITH NULL NULL
OITM OITM OITM
OSTH NULL NULL
OSTL NULL NULL
PART PART PART
PRICC01 NULL NULL
PTRM PTRM PTRM
RECS NULL NULL
RECV NULL NULL
REGN REGN REGN
SMAN SMAN SMAN
STDV STDV STDV
STIC STIC STIC
SUPFC01 NULL NULL
SUPGC01 NULL NULL
SYSC SYSC SYSC
TSPD TSPD TSPD
UACDC01 NULL NULL
UNIT UNIT UNIT
VOUTC01 NULL NULL
OREC NULL NULL
AEXTC01 NULL NULL
BANKC01 NULL NULL
ACOAC01 NULL NULL
BHDR BHDR BHDR
BSET BSET BSET
CCYDC01 NULL NULL
CCYFC01 NULL NULL
CUSFC01 NULL NULL
CUSGC01 NULL NULL
DCCT DCCT DCCT
DSTM DSTM DSTM
NULL NACEC02 NULL
NULL CCYFC02 NULL
NULL UACDC02 NULL
NULL AN2009 NULL
NULL NACGC02 NULL
NULL BG2009 NULL
NULL ACOAC02 NULL
NULL CUSGC02 NULL
NULL AC2009 NULL
NULL AEXTC02 NULL
NULL PRICC02 NULL
NULL NACAC02 NULL
NULL NACDC02 NULL
NULL SUPFC02 NULL
NULL CUSFC02 NULL
NULL CCYDC02 NULL
NULL BANKC02 NULL
NULL VOUTC02 NULL
NULL SUPGC02 NULL
NULL NULL ACOAC04
NULL NULL CUSFC04
NULL NULL BANKC04
NULL NULL VOUTC04
NULL NULL OSTL
NULL NULL SUPFC04
NULL NULL RECV
NULL NULL RECS
NULL NULL CCYDC04
NULL NULL UACDC04
NULL NULL CUSGC04
NULL NULL PRICC04
NULL NULL AEXTC04
NULL NULL NACAC04
NULL NULL NACGC04
NULL NULL NACEC04
NULL NULL CCYFC04
NULL NULL SUPGC04
NULL NULL NACDC04
if object_id('[t1]') is not null drop table [t1]
go
create table [t1]([name] varchar(4))
insert [t1]
select 'CUST' union all
select 'ADD1' union all
select 'ADD2' union all
select 'AC01' union all
select 'AC02' union all
select 'AC03'
if object_id('[t2]') is not null drop table [t2]
go
create table [t2]([name] varchar(4))
insert [t2]
select 'CUST' union all
select 'ADD1' union all
select 'ADD2' union all
select 'AC01'
if object_id('[t3]') is not null drop table [t3]
go
create table [t3]([name] varchar(4))
insert [t3]
select 'CUST' union all
select 'ADD1' union all
select 'AC01' union all
select 'AC02' union all
select 'AC03' union all
select 'AC04'
---查询---
select
t1.name as name1,
t2.name as name2,
t3.name as name3
from
(Select name From t1 ) t1
full join
(Select name From t2) t2
on
t1.name=t2.name
full join
(Select name From t3) t3
on
t2.name=t3.name or t1.name=t3.name---结果---
name1 name2 name3
----- ----- -----
CUST CUST CUST
ADD1 ADD1 ADD1
AC01 AC01 AC01
AC02 NULL AC02
AC03 NULL AC03
NULL NULL AC04
ADD2 ADD2 NULL(所影响的行数为 7 行)
4楼也错了一点,后面那个and改为or就对了