现在我有t1和t2
t1:id area cnt
101 贵州 201
102 山东 300
103 河北 99t2
id area cnt1
101 贵州 100
102 山东 12
105 济南 89
201 湖南 100怎么才能得到结果如下
101 贵州 201 100
102 山东 300 12
103 河北 99 0
105 济南 0 89
201 湖南 0 100
t1:id area cnt
101 贵州 201
102 山东 300
103 河北 99t2
id area cnt1
101 贵州 100
102 山东 12
105 济南 89
201 湖南 100怎么才能得到结果如下
101 贵州 201 100
102 山东 300 12
103 河北 99 0
105 济南 0 89
201 湖南 0 100
from
(select id,area,cnt as cnt1,0 as cnt2 from t1
union all
select id,area,0,cnt1
) t
group by id,area
if object_id('[t1]') is not null drop table [t1]
go
create table [t1]([id] int,[area] varchar(4),[cnt] int)
insert [t1]
select 101,'贵州',201 union all
select 102,'山东',300 union all
select 103,'河北',99
go
if object_id('[t2]') is not null drop table [t2]
go
create table [t2]([id] int,[area] varchar(4),[cnt1] int)
insert [t2]
select 101,'贵州',100 union all
select 102,'山东',12 union all
select 105,'济南',89 union all
select 201,'湖南',100
goselect id,area,sum(cnt1) cnt1,sum(cnt2) as cnt2
from
(select id,area,cnt as cnt1,0 as cnt2 from t1
union all
select id,area,0,cnt1 from t2
) t
group by id,area/**
id area cnt1 cnt2
----------- ---- ----------- -----------
101 贵州 201 100
103 河北 99 0
201 湖南 0 100
105 济南 0 89
102 山东 300 12(5 行受影响)
**/
drop table t1
go
if not object_id('[t2]') is null
drop table t2
go
create table t1([ID] int,[area] char(30) ,cnt int)
go
create table t2([ID] int,[area] char(30) ,cnt1 int)
go
insert t1
select 101,'贵州',201 union all
select 102,'山东',300 union all
select 103,'河北',99 go
insert t2
select 101,'贵州',100 union all
select 102,'山东',12 union all
select 105,'济南',89 union all
select 201,'湖南',100
go
select COALESCE(a.id,b.id),COALESCE(a.area,b.area),isnull(a.cnt,0),isnull(b.cnt1,0)
from t1 a full join t2 b on a.id=b.id(无列名) (无列名) (无列名) (无列名)
101 贵州 201 100
102 山东 300 12
103 河北 99 0
105 济南 0 89
201 湖南 0 100
id number,
city varchar(10),
cnt number)create table test2(
id number ,
city varchar(10),
cnt1 number)
-----------------sql statment-------------
select t2.id,t2.city,
case when t1.cnt is null then 0 else t1.cnt end cnt ,t2.cnt1 from
test1 t1 right join test2 t2 on t1.id = t2.id
union
select t1.id,t1.city,t1.cnt,case when t2.cnt1 is null then 0 else t2.cnt1 end cnt1
from
test1 t1 left join test2 t2 on t1.id = t2.id
from t1, t2
where t1.id = t2.id
union
select t1.id, t1.area, t1.cnt, 0
from t1
where not exists(select 1 from t2 where t1.id = t2.id)
union
select t2.id, t2.area, 0, t2.cnt1
from t2
where not exists(select 1 from t1 where t1.id = t2.id)
-----sinpoal
create table test1(
id number,
city varchar(10),
cnt number)create table test2(
id number ,
city varchar(10),
cnt1 number)insert test1
select 101,'贵州',201 union all
select 102,'山东',300 union all
select 103,'河北',99
insert test2
select 101,'贵州',100 union all
select 102,'山东',12 union all
select 105,'济南',89 union all
select 201,'湖南',100----------sql statement--------------
select t2.id,t2.city,
case when t1.cnt is null then 0 else t1.cnt end cnt ,t2.cnt1 from
test1 t1 right join test2 t2 on t1.id = t2.id
union
select t1.id,t1.city,t1.cnt,
case when t2.cnt1 is null then 0 else t2.cnt1 end cnt1
from
test1 t1 left join test2 t2 on t1.id = t2.id
go
create table [t1]([id] int,[area] nvarchar(4),[cnt] int)
insert [t1]
select 101,N'贵州',201 union all
select 102,N'山东',300 union all
select 103,N'河北',99
go
if object_id('[t2]') is not null drop table [t2]
go
create table [t2]([id] int,[area] nvarchar(4),[cnt1] int)
insert [t2]
select 101,N'贵州',100 union all
select 102,N'山东',12 union all
select 105,N'济南',89 union all
select 201,N'湖南',100
GOSELECT * FROM t1 t
SELECT * FROM t2 pSELECT DISTINCT * FROM
(
SELECT t.id,t.area,t.cnt AS cnt,isnull(p.cnt1,0) AS cnt1
FROM t1 t
left join t2 p
ON t.id=p.id
UNION ALL
SELECT l.id,l.area,isnull(h.cnt,0) AS cnt ,l.cnt1 AS cnt1
FROM t2 l
left join t1 h
ON l.id=h.id) M
ORDER BY M.idid area cnt cnt1
----------- ---- ----------- -----------
101 贵州 201 100
102 山东 300 12
103 河北 99 0
105 济南 0 89
201 湖南 0 100(5 row(s) affected)