学生成绩表1
学号 A学科成绩
1 80
2 75
4 90学生成绩表2
学号 B学科成绩
1 65
3 70
4 80
5 45
6 82
需要结果
学号 A学科成绩 B学科成绩
1 80 65
2 75 0
3 0 70
4 90 80
5 0 45
6 0 82
create table table1
(
s_id int not null,
s_numberA int null
)
insert into table1 values(1,80)
insert into table1 values(2,75)
insert into table1 values(4,90)create table table2
(
s_id int not null,
s_numberB int null
)
insert into table2 values (1,60)
insert into table2 values(3,70)
insert into table2 values (4,80)
insert into table2 values (5,45)
insert into table2 values(6,82)
学号 A学科成绩
1 80
2 75
4 90学生成绩表2
学号 B学科成绩
1 65
3 70
4 80
5 45
6 82
需要结果
学号 A学科成绩 B学科成绩
1 80 65
2 75 0
3 0 70
4 90 80
5 0 45
6 0 82
create table table1
(
s_id int not null,
s_numberA int null
)
insert into table1 values(1,80)
insert into table1 values(2,75)
insert into table1 values(4,90)create table table2
(
s_id int not null,
s_numberB int null
)
insert into table2 values (1,60)
insert into table2 values(3,70)
insert into table2 values (4,80)
insert into table2 values (5,45)
insert into table2 values(6,82)
ISNULL(A.s_id,B.s_id) AS s_id,
ISNULL(A.s_numberA,0) AS s_numberA,
ISNULL(B.s_numberB,0) AS s_numberB
FROM table1 AS A
FULL JOIN table2 AS B
ON A.s_id=B.s_id
create table table1
(
s_id int not null,
s_numberA int null
)
insert into table1 values(1,80)
insert into table1 values(2,75)
insert into table1 values(4,90) create table table2
(
s_id int not null,
s_numberB int null
)
insert into table2 values (1,60)
insert into table2 values(3,70)
insert into table2 values (4,80)
insert into table2 values (5,45)
insert into table2 values(6,82)select t2.s_id ,isnull(t1.s_numberA,0),t2.s_numberB table2 t2,table1 t1 where t1.s_id =t2.s_id