Question

如果一个表中两个字段都受另一个表的一个字段约束的sql语句

如：changeclass表中有字段：oldcid（原班id）,newcid（新班id）, class表中有 cid，cname.
其中oldcid,newcid,都受clid约束,现在要在显出结果是 :原班名，新班名
这个sql语句应该怎么写啊
求高手赐教啊

Answer 1

--创建表
create table changeclass
(
oldcid int,
newcid int
)
--插入数据
insert into changeclass
select 1,4
union
select 2,3
union
select 3,2
union
select 4,1
--创建班级表
create table class
(
cid int,
cname nvarchar(50)
)
--插入数据
insert into class
select 1,'A-Class'
union
select 2,'B-Class'
union
select 3,'C-Class'
union
select 4,'D-Class'
--获得结果
select b.cname as 原班名,
c.cname as 新班名
from changeclass a
left outer join
class b
on a.oldcid = b.cid
left outer join
class c
on a.newcid=c.cid

Answer 2

SELECT CC.oldcid, CC.newcid, CC.changedate, C1.cname AS oldcname, C2.cname AS newcname
FROM class AS C2 INNER JOIN
changeclass AS CC INNER JOIN
class AS C1 ON CC.oldcid = C1.cid ON C2.cid = CC.newcid