唉！这个Sql语句应该不会很难吧（100分啦！！）
春节还要加班！昨天已经熬了一夜了，好烦哦，
各位大哥哥，快来帮帮我啊，偶快被老板炒鱿鱼了啊:(
有三个表：
第一个表：basicinfo
sid name
1 Jack
2 Tom
第二个表：grade1
sid course score
1 math 91
2 math 80
2 computer 80
第三个表：grade2
sid course score
1 english 93
我想通过Sql语句分别得到下面两个结果，这个sql语句该怎么写啊，烦死了!!
（关键是如何把表名grade1和grade2放在结果集里呀？？）
sid name course grade score
1 Jack math grade1 91
1 Jack english grade2 93
sid name course grade score
2 Tom math grade1 80
2 Tom computer grade1 80
怎么办啊，都夜里两点啦，好困哦，帮帮我啦！！！！！！！！拜托啦！！
偶先呼呼了~~~~
[解决办法]
create table #tmpGrade(sid int, course varchar(10), grade varchar(10), score int)
insert into #tmpGrade select sid, course, 'grade1 ', score from grade1
insert into #tmpGrade select sid, course, 'grade2 ', score from grade2
select t1.sid, t1.[name], t2.course, t2.grade, t2.score
from basicinfo t1, #tmpGrade t2 where t1.sid = t2.sid and t1.[name] = 'Jack '
select t1.sid, t1.[name], t2.course, t2.grade, t2.score
from basicinfo t1, #tmpGrade t2 where t1.sid = t2.sid and t1.[name] = 'Tom '
[解决办法]
select A.sid,A.name,B.course,B.grade,B.score
from basicinfo A
inner join
(select *, 'grade1 ' as grade from grade1
union all
select *, 'grade2 ' as grade from grade2 ) B on A.sid=B.sid
where A.sid=1
select A.sid,A.name,B.course,B.grade,B.score
from basicinfo A
inner join
(select *, 'grade1 ' as grade from grade1
union all
select *, 'grade2 ' as grade from grade2 ) B on A.sid=B.sid
where A.sid=2
[解决办法]
create table basicinfo(sid int, name varchar(50))
insert basicinfo select 1, 'Jack '
union all select 2, 'Tom '
go
create table grade1(sid int, course varchar(50), score int)
insert grade1 select 1, 'math ', 91
union all select 2, 'math ', 80
union all select 2, 'computer ', 80
go
create table grade2(sid int, course varchar(50), score int)
insert grade2 select 1, 'english ', 93
go
select a.*, b.course, b.grade, b.score from basicinfo a
inner join
(
select sid, course, score, grade= 'grade1 ' from grade1
union all
select sid, course, score, grade= 'grade2 ' from grade2
)b on a.sid=b.sid
where a.sid=1
go
select a.*, b.course, b.grade, b.score from basicinfo a
inner join
(
select sid, course, score, grade= 'grade1 ' from grade1
union all
select sid, course, score, grade= 'grade2 ' from grade2
)b on a.sid=b.sid
where a.sid=2
go
[解决办法]
第一个表：
create table basicinfo
(
sid smallint,
name varchar(20)
)
insert into basicinfo
select 1, 'Jack ' union all
select 2, 'Tom '
第二个表：
create table grade1
(
sid smallint,
course varchar(10),
score smallint
)
insert into grade1
select 1, 'math ', 91 union all
select 2, 'math ', 80 union all
select 2, 'computer ' , 80
第三个表：
create table grade2
(
sid smallint,
course varchar(10),
score smallint
)
insert into grade2
select 1, 'english ', 93
select a.sid,a.name,b.course, 'grade1 'as grade1,b.score from basicinfo a join grade1 b on a.sid=b.sid
union
select a.sid,a.name,b.course, 'grade2 'as grade2,b.score from basicinfo a join grade2 b on a.sid=b.sid
sid name course grade1 score
------ -------------------- ---------- ------ ------
1 Jack english grade2 93
1 Jack math grade1 91
2 Tom computer grade1 80
2 Tom math grade1 80
(4 row(s) affected)
