现有表1表2
table11,table2table1:
id value1
1 100
2 200
3 700table2:
id value2
1 98
3 99我想得到的查询结果为
id value1 value2
1 100 98
2 200 0 (或者显示null也行)
3 700 99sql该如何写?
而我实际得到结果中没有2号记录。
table11,table2table1:
id value1
1 100
2 200
3 700table2:
id value2
1 98
3 99我想得到的查询结果为
id value1 value2
1 100 98
2 200 0 (或者显示null也行)
3 700 99sql该如何写?
而我实际得到结果中没有2号记录。
select a.id,a.value1,b.value2 from table1 a left join table2 b on a.id=b.id
isnull(a.id,b.id) as id,isnull(a.value1,0) as value1,
isnull(b.value2,0) as value2
from
table1 a full join table2 b
on
a.id=b.id
if object_id('table1') is not null
drop table table1
go
create table table1
(
id int,
value1 int
)
go
insert into table1
select 1,100 union all
select 2,200 union all
select 3,700
go
if object_id('table2') is not null
drop table table2
go
create table table2
(
id int,
value2 int
)
go
insert into table2
select 1,98 union all
select 3,99
go
select a.id,a.value1,b.value2 from table1 a left join table2 b on a.id=b.id --显示nullselect a.id,a.value1,value2=isnull(b.value2,0) from table1 a left join table2 b on a.id=b.id --显示0/*
id value1 value2
----------- ----------- -----------
1 100 98
2 200 0
3 700 99(3 行受影响)*/
insert into table1 select 1,100
insert into table1 select 2,200
insert into table1 select 3,700
create table table2(id int,value2 int)
insert into table2 select 1,98
insert into table2 select 3,99
go
select a.id,a.value1,b.value2 from table1 a left join table2 b on a.id=b.id
/*
id value1 value2
----------- ----------- -----------
1 100 98
2 200 NULL
3 700 99(3 行受影响)*/
go
drop table table1,table2
我原来用的就是左连接,left outer join
其实前面我为了把问题说的简单明了一些,就简化了。其实我是把table2做sum求和了,table2原始的数据是:
table2:
id value2
1 12
1 33
3 87
3 9table1:
id value1
1 100
2 200
3 700
4 504
我的sql 是:
select a.id,a.value1,sum(b.value2) from table1 a left outer join table2 b on a.id=b.id where a.id in ('1','2','3') group by a.id,b.id,a.value1 这样结果中还是没有id=2的记录