求一条简单的SQL语句(50分)

  • 主题发起人 主题发起人 shbx_sjuser
  • 开始时间 开始时间
S

shbx_sjuser

Unregistered / Unconfirmed
GUEST, unregistred user!
SYBASE数据库,A,B表,<br>A表:<br>ps1 &nbsp; ps2<br>001 &nbsp; XIN<br>002 &nbsp; XXX<br>B表<br>NM1 &nbsp; NM2 &nbsp; &nbsp; &nbsp; &nbsp; NM3<br>001 &nbsp; 2005-1-1 &nbsp; &nbsp;288.00<br>001 &nbsp; 2007-1-1 &nbsp; &nbsp;340.00<br>002 &nbsp; 2003-1-1 &nbsp; &nbsp;299.00<br>002 &nbsp; 2004-1-1 &nbsp; &nbsp;200.00<br>求一条SQL语句能得出B表中A表对应的ID号在B表最大的时间那条记录.<br>ps1 &nbsp; ps2 &nbsp;NM2 &nbsp; &nbsp; &nbsp; NM3<br>001 &nbsp; XIN &nbsp;2007-1-1 &nbsp;340.00<br>002 &nbsp; XXX &nbsp;2004-1-1 &nbsp;200.00
 
1、oracle 写法<br>SELECT A.PS1, A.PS2, B.NM2, B.NM3<br>FROM &nbsp; B LEFT &nbsp; OUTER JOIN A ON B.NM1 = A.PS1<br>WHERE &nbsp;(B.NM1, B.NM2) IN (SELECT NM1, MAX(NM2) AS NM2 FROM B GROUP BY NM1) <br><br>2、SQLServer 写法<br>SELECT A.PS1, A.PS2, B.NM2, B.NM3<br>FROM &nbsp; B LEFT &nbsp; OUTER JOIN A ON B.NM1 = A.PS1<br>WHERE &nbsp;B.NM + '@' + B.NM2 IN (SELECT NM1 +'@'+ MAX(NM2) &nbsp;FROM B GROUP BY NM1)
 
ORACLE:<br>select A.PS1, A.PS2, B.NM2, B.NM3<br>from A,B<br>where A.PS1 = B.NM1<br>&nbsp; &nbsp; &nbsp; and B.NM1||B.NMm2 in <br>&nbsp; &nbsp; &nbsp; (select B.NM1||max(B.NM2) NM2 &nbsp;from B<br>&nbsp; &nbsp; &nbsp; &nbsp;group by B.NM1)
 
楼上大哥,没有SYBASE写法啊?我把你的两句测试都过不了....
 
SQL 写法<br><br>&nbsp; &nbsp; &nbsp;select a.*,b.nm2,b.nm3 from &nbsp;a left join<br>&nbsp; &nbsp; (<br>&nbsp; &nbsp; select nm1 ,nm2,max(nm3) as nm3 from<br>&nbsp; &nbsp; (<br>&nbsp; &nbsp; select a.nm1,a.nm2,b.nm3 from<br>&nbsp; &nbsp; (<br>&nbsp; &nbsp; &nbsp;select nm1,max(nm2) as nm2 from b group by nm1<br>&nbsp; &nbsp; ) a left join b on (a.nm1 = b.nm1 and a.nm2 = b.nm2)<br>&nbsp; &nbsp; ) a<br>&nbsp; &nbsp; group by nm1,nm2<br>&nbsp; &nbsp; ) b on a.ps1 = b.nm1<br>我测试通过了,你可以试验一下
 
步骤分解:<br>1、求出B中最大时间的那些记录假定为C表<br>SELECT * FROM B<br>WHERE &nbsp;(B.NM1, B.NM2) IN (SELECT NM1, MAX(NM2) AS NM2 FROM B GROUP BY NM1) <br><br>2、求A与C表的内关联记录<br>SELECT A.*, C.NM2,C,NM3<br>FROM A , C<br>where A.ps1 =C.Num1<br><br>3、将1步骤中的语句代替步骤2的中C表<br>SELECT A.*, C.NM2, C, NM3<br>FROM &nbsp; A,<br>&nbsp; &nbsp; &nbsp; &nbsp;(SELECT *<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FROM &nbsp; B<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WHERE &nbsp;(B.NM1, B.NM2) IN<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (SELECT NM1, MAX(NM2) AS NM2 FROM B GROUP BY NM1)) C<br>WHERE &nbsp;A.PS1 = C.NUM1
 
Select A.ps1,A.ps2,c.nm2,c.nm3 From (A Inner Join (Select * from b _b &nbsp;where nm2 in (Select Max(nm2) &nbsp;From b &nbsp;where b.nm1=_b.nm1)) As c On A.ps1=c.nm1)
 
介绍个DELPHI博客, 自己去找找看.<br><br>http://iinsnian.cnblogs.com<br><br>如果运气好的话,也许找得到你要的答案。:-)
 
ww0219,SELECT * FROM B<br>WHERE &nbsp;(B.NM1, B.NM2) IN (SELECT NM1, MAX(NM2) AS NM2 FROM B GROUP BY NM1)这句在pb里执行提示','附近出现错误,我改用WHERE &nbsp;(B.NM1+convert(char(10),B.NM2,112)也不能通过,如果在PB里SQL能同时in两个字段,那问题早解决了
 
后退
顶部