select isnull(a.bh,b.bh) as bh,a.sj as [a_sj],b.sj as [b_sj] from a,b where 1=1
select ISNULL(a.bh,B.BH)BH,ISNULL(a.sj,0) as [a_sj],ISNULL(b.sj,0) as [b_sj] from a FULL JOIN b ON a.bh = b.bh
---------------------------------------------------------------- -- Author :fredrickhu(小F 向高手学习) -- Date :2009-06-23 14:49:19 ---------------------------------------------------------------- --> 测试数据:[a1] if object_id('[a1]') is not null drop table [a1] create table [a1]([bh] int,[sj] int) insert [a1] select 1,1000 union all select 2,2000 union all select 3,4000 --> 测试数据:[b2] if object_id('[b2]') is not null drop table [b2] create table [b2]([bh] int,[sj] int) insert [b2] select 4,5000 --------------开始查询--------------------------select * from [a1] union all select * from [b2] ----------------结果---------------------------- /*bh sj 1 1000 2 2000 3 4000 4 5000*/
--看错 SORRY ---------------------------------------------------------------- -- Author :fredrickhu(小F 向高手学习) -- Date :2009-06-23 14:51:19 ---------------------------------------------------------------- --> 测试数据:[a1] if object_id('[a1]') is not null drop table [a1] create table [a1]([bh] int,[sj] int) insert [a1] select 1,1000 union all select 2,2000 union all select 3,4000 --> 测试数据:[b2] if object_id('[b2]') is not null drop table [b2] create table [b2]([bh] int,[sj] int) insert [b2] select 4,5000 --------------开始查询--------------------------select isnull(a.bh,b.bh) as bh,a.sj as [a_sj],b.sj as [b_sj] from a1 a,b2 b where 1=1 ----------------结果---------------------------- /*bh a_sj b_sj 1 1000 5000 2 2000 5000 3 4000 5000 */
select bh,sj a_sj,0 as b_sj from a union all select bh,0,sj from b
create table [a]([bh] int,[sj] int) insert [a] select 1,1000 union all select 2,2000 union all select 3,4000create table [b]([bh] int,[sj] int) insert [b] select 4,5000select bh,sj a_sj,0 as b_sj from a union all select bh,0,sj from b /* bh a_sj b_sj ----------- ----------- ----------- 1 1000 0 2 2000 0 3 4000 0 4 0 5000(4 行受影响)*/
-- 先构造基础数据 select * into #t1 from ( select bh, sj, fromtype='a' from a union select bh, sj, fromtype='b' from b union select bh, sj, fromtype='c' from c union select bh, sj, fromtype='d' from d ) as a-- 再就预先构造的基础数据进行行列转换 ...
--測試數據 declare @table1 table([bh] int,[sj] int) insert @table1 select 1,1000 union all select 2,2000 union all select 3,4000 declare @table2 table ([bh] int,[sj] int) insert @table2 select 4,5000 --查詢 select * from ( select bh,sj as a_sj,0 as b_sj from @table1 union all select bh,0 as a_sj,sj as b_sj from @table2) t1
from a,b
where 1=1
from a FULL JOIN b
ON a.bh = b.bh
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-06-23 14:49:19
----------------------------------------------------------------
--> 测试数据:[a1]
if object_id('[a1]') is not null drop table [a1]
create table [a1]([bh] int,[sj] int)
insert [a1]
select 1,1000 union all
select 2,2000 union all
select 3,4000
--> 测试数据:[b2]
if object_id('[b2]') is not null drop table [b2]
create table [b2]([bh] int,[sj] int)
insert [b2]
select 4,5000
--------------开始查询--------------------------select * from [a1]
union all
select * from [b2]
----------------结果----------------------------
/*bh sj
1 1000
2 2000
3 4000
4 5000*/
----------------------------------------------------------------
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-06-23 14:51:19
----------------------------------------------------------------
--> 测试数据:[a1]
if object_id('[a1]') is not null drop table [a1]
create table [a1]([bh] int,[sj] int)
insert [a1]
select 1,1000 union all
select 2,2000 union all
select 3,4000
--> 测试数据:[b2]
if object_id('[b2]') is not null drop table [b2]
create table [b2]([bh] int,[sj] int)
insert [b2]
select 4,5000
--------------开始查询--------------------------select isnull(a.bh,b.bh) as bh,a.sj as [a_sj],b.sj as [b_sj]
from a1 a,b2 b
where 1=1
----------------结果----------------------------
/*bh a_sj b_sj
1 1000 5000
2 2000 5000
3 4000 5000
*/
union all
select bh,0,sj from b
insert [a]
select 1,1000 union all
select 2,2000 union all
select 3,4000create table [b]([bh] int,[sj] int)
insert [b]
select 4,5000select bh,sj a_sj,0 as b_sj from a
union all
select bh,0,sj from b
/*
bh a_sj b_sj
----------- ----------- -----------
1 1000 0
2 2000 0
3 4000 0
4 0 5000(4 行受影响)*/
a表:
bh sj
--------------------
1 1000
2 2000
3 4000 b表:
bh sj
-------------------
4 5000 c表:无数据
d表:无数据
结果:
我想要的结果:
bh a_sj b_sj c_sj d_sj
-------------------------------------------------
1 1000 0 0 0
2 2000 0 0 0
3 3000 0 0 0
4 0 5000 0 0
select * into #t1 from (
select bh, sj, fromtype='a' from a union
select bh, sj, fromtype='b' from b union
select bh, sj, fromtype='c' from c union
select bh, sj, fromtype='d' from d
) as a-- 再就预先构造的基础数据进行行列转换
...
declare @table1 table([bh] int,[sj] int)
insert @table1
select 1,1000 union all
select 2,2000 union all
select 3,4000
declare @table2 table ([bh] int,[sj] int)
insert @table2
select 4,5000
--查詢
select *
from (
select bh,sj as a_sj,0 as b_sj from @table1
union all
select bh,0 as a_sj,sj as b_sj from @table2) t1