这种SQL 视图创建语句该如何写? (近来发现大富翁人气不够旺?为什么?) ( 积分: 100 )

有两表:<br>&nbsp;&nbsp;TABLE1:<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Autoid&nbsp;int&nbsp;identity(1,1)&nbsp;Primary&nbsp;Key,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;OldJobid&nbsp;integer,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;NewJobid&nbsp;integer;<br>&nbsp;&nbsp;TABLE2:<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Autoid&nbsp;int&nbsp;identity(1,1)&nbsp;Primary&nbsp;Key,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;JobName&nbsp;String(30)<br>现在要建立一个视图,建立Table1的OldJobid及NewJobid与Table2的Autoid关联,分别显示出JobName.即最终表字段为:<br>&nbsp;&nbsp;&nbsp;&nbsp;Table1.Autoid&nbsp;Table1.OldJobid,Table2.JobName&nbsp;As&nbsp;OldJobName,Table1.NewJobid,Tabel1.JobName&nbsp;As&nbsp;NewJob<br><br>请高手指点!!!

create&nbsp;view&nbsp;view1&nbsp;<br>as<br>select&nbsp;table1.autoid,table1.oldjobid,table2.jobname&nbsp;as&nbsp;oldjobname,table1.newjobid,table2.jobname&nbsp;as&nbsp;newjob&nbsp;from&nbsp;table1<br>join&nbsp;table2&nbsp;on&nbsp;table1.oldjobid=table2.autoid<br>&nbsp;<br>不晓得你说的是不是这个意思。就是把jobname取2次as&nbsp;成不同的名字就成

create&nbsp;view&nbsp;view1&nbsp;<br>as<br>select&nbsp;table1.autoid,table1.oldjobid,'oldjobname'=(select&nbsp;jobname&nbsp;from&nbsp;table2&nbsp;where&nbsp;autoid=oldjobid),table1.newjobid,'newjobname'=(select&nbsp;jobname&nbsp;from&nbsp;table2&nbsp;where&nbsp;autoid=newjobid)

shadowpj:<br>&nbsp;&nbsp;你的方法最好显示结果为,oldjobname与Newjobname值一样!该方法行不通!<br>银雨辰:<br>&nbsp;&nbsp;你的方法能达到目的,但不知执行效率如何?如果用left&nbsp;join的话,那语句该如何写呢?

SELECT&nbsp;a.autoid,&nbsp;b.jobname&nbsp;as&nbsp;oldjobname,&nbsp;c.jobname&nbsp;as&nbsp;newjobname&nbsp;<br>FROM&nbsp;table1&nbsp;a<br>LEFT&nbsp;JOIN&nbsp;table2&nbsp;b<br>ON&nbsp;a.OldJobid&nbsp;=&nbsp;b.Autoid<br>LEFT&nbsp;JOIN&nbsp;table2&nbsp;c<br>ON&nbsp;a.newjobid&nbsp;=&nbsp;c.Autoid

在看到答案时,我也是想到这种方法,并采用了这种方法,还是谢谢银雨辰!