求个SQL查询 300分(200分)

TAB1 <br>NO &nbsp;H1 &nbsp; H2 <br>----------------<br>1 &nbsp;123 &nbsp; 434<br>2 &nbsp;213 &nbsp; 34 <br>3 &nbsp;342 &nbsp;3123<br>4 &nbsp;231 &nbsp; 213<br>------------------<br>NO值无重复<br><br>TAB2 &nbsp;<br>NO &nbsp;MX1 &nbsp; MX2 &nbsp; &nbsp;<br>----------------<br>1 &nbsp;123 &nbsp; 434 &nbsp; <br>2 &nbsp;213 &nbsp; 34 <br>2 &nbsp;342 &nbsp;2321<br>2 &nbsp;231 &nbsp; 3213<br>3 &nbsp;123 &nbsp;143<br>3 &nbsp;213 &nbsp;4334<br>3 &nbsp;435 &nbsp;4234<br>4 &nbsp;3213 &nbsp;21312<br>............<br>NO 值有重复 并且相同NO的MX1 &nbsp;MX2不一定相同<br><br><br>需要的查询结果 &nbsp;<br><br>需要的结果<br>NO &nbsp;H1 &nbsp; H2 &nbsp; &nbsp; MX1 &nbsp; &nbsp; MX2 &nbsp;<br>----------------------------<br>1 &nbsp;123 &nbsp; 434 &nbsp; &nbsp;123 &nbsp; &nbsp;434 <br>2 &nbsp;213 &nbsp; &nbsp;34 &nbsp; &nbsp;213 &nbsp; &nbsp; 34 <br>3 &nbsp;342 &nbsp;3123 &nbsp; &nbsp;123 &nbsp; &nbsp;143<br>4 &nbsp;231 &nbsp; 213 &nbsp; 3213 &nbsp;21312<br>..........................<br>TAB1.NO &nbsp;等于 TAB2.NO 的 只取TAB2的第一条 TAB2. MX1 &nbsp;TAB2.MX2

Select OB=Identity(int,1,1),* into #tb From Tab2<br>Select Tab1.*,#tb.MX1,#tb.MX2<br>from Tab1<br>left join<br>&nbsp; &nbsp; (Select #tb.no,#tb.MX1,#tb.MX2<br>&nbsp; &nbsp; &nbsp;from (Select no,min(OB)<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;from #tb<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;group by no) b,#tb<br>&nbsp; &nbsp; &nbsp;where b.no=#tb.no<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;and b.OB=#tb.OB) c<br>on Tab1.no=c.no<br>--Drop table #tb<br><br>mmo:未测试！？

3q 用临时表或视图的方法是很容易实现的，但目前不考虑用临时表或视图

select &nbsp;distinct(tab1.no),tab1.h1,tab1.h2,tab2.MX1,tab2.MX2 from tab1 left join tab2 on tab1.no = tab2.no

已经求证过的。。<br><br>drop table #tb<br>Select OB=Identity(int,1,1),* into #tb From TAB2 ORDER BY TAB2.NO ASC<br><br>select TAB1.NO,TAB1.H1,TAB1.H2,b.MX1U,b.MX2U FROM TAB1 left join <br>( select s.NO as d,#tb.MX1 as MX1U,#tb.MX2 AS MX2U from #tb left join <br>(SELECT min(#tb.OB) AS a,#tb.NO FROM #tb group by #tb.NO)s on #tb.OB=s.a )b on TAB1.NO=b.d

select a.*，<br>(select top1 Mx1 from tab2 where No=a.No) as Mx1，<br>(select top1 Mx2 from tab2 where No=a.No) as Mx2，<br>from Tab1 a<br>没那么复杂吧

谢谢大家 这个问题已经解决了 方法与ldq-11雷同 祝大家工作开心[]

恩,解决了就好.<br>不过对于子查询,最好使用left join来优化.这样子效率会高一点

其实也简单，就取头一个就行了呗

..

3Q