两个表
STUDENT_TAB
===========
SID SNAME SADDRESS
---------------------------------------
S0001 A DDDDDDD
S0002 B DDDDDDD
S0003 C DDDDDDD
S0004 D DDDDDDD
S0005 E DDDDDDD
S0006 F DDDDDDD
S0007 G DDDDDDDSCORE_TAB
===========
SID SCORE
---------------------------------
S0001 56
S0002 68
S0003 88
S0005 20
S0006 40就上面两个表格,面试的要求是查询出
SELECT SNAME,SCORE FROM STUEDENT_TAB,SCORE_TAB
查询结果中要求包含SCORE_TAB中STUDENT_TAB成绩没有SNAME和SCORE。我自己下来也试了下,SQL语句后来整的太复杂了。求简单可行的方法。
STUDENT_TAB
===========
SID SNAME SADDRESS
---------------------------------------
S0001 A DDDDDDD
S0002 B DDDDDDD
S0003 C DDDDDDD
S0004 D DDDDDDD
S0005 E DDDDDDD
S0006 F DDDDDDD
S0007 G DDDDDDDSCORE_TAB
===========
SID SCORE
---------------------------------
S0001 56
S0002 68
S0003 88
S0005 20
S0006 40就上面两个表格,面试的要求是查询出
SELECT SNAME,SCORE FROM STUEDENT_TAB,SCORE_TAB
查询结果中要求包含SCORE_TAB中STUDENT_TAB成绩没有SNAME和SCORE。我自己下来也试了下,SQL语句后来整的太复杂了。求简单可行的方法。
=============
一个学生表,一个成绩表。要求将查询所有学生的成绩出来(其中要求包含了那些有成绩的学生和没有成绩的学生的信息),结果包含的列为SNAME和SCORE。
--> 测试数据: [STUDENT_TAB]
if object_id('[STUDENT_TAB]') is not null drop table [STUDENT_TAB]
create table [STUDENT_TAB] (SID varchar(5),SNAME varchar(1),SADDRESS varchar(7))
insert into [STUDENT_TAB]
select 'S0001','A','DDDDDDD' union all
select 'S0002','B','DDDDDDD' union all
select 'S0003','C','DDDDDDD' union all
select 'S0004','D','DDDDDDD' union all
select 'S0005','E','DDDDDDD' union all
select 'S0006','F','DDDDDDD' union all
select 'S0007','G','DDDDDDD'
--> 测试数据: [SCORE_TAB]
if object_id('[SCORE_TAB]') is not null drop table [SCORE_TAB]
create table [SCORE_TAB] (SID varchar(5),SCORE int)
insert into [SCORE_TAB]
select 'S0001',56 union all
select 'S0002',68 union all
select 'S0003',88 union all
select 'S0005',20 union all
select 'S0006',40--开始查询
select a.*,b.SCORE from [STUDENT_TAB] a left join [SCORE_TAB] b
on (a.SID=b.SID)--结束查询
drop table [STUDENT_TAB],[SCORE_TAB]/*
SID SNAME SADDRESS SCORE
----- ----- -------- -----------
S0001 A DDDDDDD 56
S0002 B DDDDDDD 68
S0003 C DDDDDDD 88
S0004 D DDDDDDD NULL
S0005 E DDDDDDD 20
S0006 F DDDDDDD 40
S0007 G DDDDDDD NULL(7 行受影响)
这题就是考你会不会left join啊
[CODE = SQL CODE]
SELECT A.SNAME, B.SCORE
FROM STUDENT_TAB AS A
LEFT JOIN SCORE_TAB AS B ON A.SID = B.SID
[/CODE]
insert into student_tab select 'S0001','A','DDDDDDD'
insert into student_tab select 'S0002','B','DDDDDDD'
insert into student_tab select 'S0003','C','DDDDDDD'
insert into student_tab select 'S0004','D','DDDDDDD'
insert into student_tab select 'S0005','E','DDDDDDD'
insert into student_tab select 'S0006','F','DDDDDDD'
insert into student_tab select 'S0007','G','DDDDDDD'
create table SCORE_TAB(SID varchar(10),SCORE int)
insert into score_tab select 'S0001',56
insert into score_tab select 'S0002',68
insert into score_tab select 'S0003',88
insert into score_tab select 'S0005',20
insert into score_tab select 'S0006',40
go
SELECT a.SNAME,b.SCORE FROM STUDENT_TAB a left join SCORE_TAB b on a.sid=b.sid
/*
SNAME SCORE
---------- -----------
A 56
B 68
C 88
D NULL
E 20
F 40
G NULL(7 行受影响)*/
go
drop table STUDENT_TAB,score_tab