一个一对多的查询搞不定，请求帮助

Question

有一仓库表Depot(cId 编号cName名称)
cId       cName
01        一仓库
02        二仓库
03        三仓库
04        四仓库另一仓库调拨表DepotMove(cId单据号DepotIdFrom调出仓库Id  DeportIdTo调入仓库Id)
cId         DepotIdFrom        DeportIdTo
DB040001      01                   02
DB040002      01                   04
BD050001      03                   01
DB050002      04                   02请问要在达到下面的查询效果如果写这个SQL，即同时将仓库的ID和Name显示出来
cId         DepotIdFrom   DepotNameFrom      DeportIdTo   DepotNameTo
DB040001      01           一仓库                02         二仓库
DB040002      01           一仓库                04         四仓库
BD050001      03           三仓库                01         一仓库
DB050002      04           四仓库                02         二仓库

Answer 1

create table depot(cid nvarchar(2),cname nvarchar(20))
insert into depot select '01',N'一仓库'
        union all select '02',N'二仓库'
        union all select '03',N'三仓库'
        union all select '04',N'四仓库'
create table depotmove(cid nvarchar(15),depotidfrom nvarchar(2),depotidto nvarchar(2))
insert into depotmove select 'DB040001','01','02'
            union all select 'DB040002','01','04'
            union all select 'BD050001','03','01'
            union all select 'DB050002','04','02'select b.cid,b.depotidfrom,a.cname,b.depotidto,c.cname from depot a
inner join depotmove b on b.depotidfrom=a.cid
inner join depot c on b.depotidto=c.cid
order by depotidfrom drop table depot,depotmove
---------------------------------
DB040001 01  一仓库 02 二仓库
DB040002 01  一仓库 04 四仓库
BD050001 03  三仓库 01 一仓库
DB050002 04  四仓库 02 二仓库

Answer 2

select T2.cid,T2.depotidfrom,T1.cname,T2.depotidto,T3.cname from depot T1
inner join depotmove T2 on T2.depotidfrom=T1.cid
inner join depot T3 on T2.depotidto=T3.cid
order by depotidfrom