[转载] SQL应用--查询最大版本值
<P>信息来源: <A id=ArticleTitle1_ArticleTitle1_AuthorLink href="http://dev.csdn.net/user/feng19821209">feng19821209</A> 的 Blog </P><P><SPAN id=ArticleContent1_ArticleContent1_lblContent> </P>
<P>EmsNo CopGNo ExgVersion </P>
<P>E57705000004 DSHC0741 60 <BR>E57705000003 DSHC0741 50 <BR>E57705000002 DSHC0741 36 <BR>E57705000001 DSHC0742 65 <BR>E57705000000 DSHC0742 75 <BR>需要的结果：<BR>EmsNo CopGNo ExgVersion <BR><BR>E57705000004 DSHC0741 60 <BR>E57705000000 DSHC0742 75 <BR>既 CopGNO中每个产品的最大ExgVersion给取出来。</P>
<P>SELECT T.EMSNO,E.COPGNO,E.EXGVERSION ,T.begindate,t.enddate<BR>FROM emsexgversion T ,(SELECT copgno,MAX(ExgVersion) as exgversion from emsexgversion group by copgno) E <BR>WHERE T.COPGNO=E.COPGNO AND T.EXGVERSION=E.EXGVERSION<BR>ORDER BY E.COPGNO</P>
<P>select a.* from emsexgversion a <BR>where not exists(select 1 from emsexgversion where CopGNo=a.CopGNo and ExgVersion>a.ExgVersion)</P>
<P>select 1 from ...<BR>通常存在于exists中.<BR>也可以写做<BR>select 2 from ...<BR>或者任何常数.</P>
<P>因为exists只是判断为不为null来返回true或者false<BR>通常的写法还有<BR>select count(1) from ...<BR>其实等同于<BR>select count(*) from ...<BR>或者<BR>select count(主键) from ...<BR>这样的SQL</P>
<P>根据主键及索引的建立方式,速度上有细小的差别.</P>
<P>而-1 = -1这样的表达式是永远返回true的.<BR>就像 1 = 2是永远返回false一样.</P>
<P>我们通常copy表结构就经常用<BR>create table newtable as<BR>select * from oldtable where 1 = 2;</P>
<P>-1 = -1一般是写在条件不确定的用字符串拼出来的动态SQL中.<BR>比方说:<BR>我的where后面可能有5个条件,也可能一个也没有.<BR>这时我会这样写<BR>".... where 1 = 1"+str1+str2+...; -- 其中的str1,str2等有可能是空字符串.</P></SPAN>
页:
[1]