SELECT T.EMSNO,E.COPGNO,E.EXGVERSION ,T.begindate,t.enddate FROM emsexgversion T ,(SELECT copgno,MAX(ExgVersion) as exgversion from emsexgversion group by copgno) E WHERE T.COPGNO=E.COPGNO AND T.EXGVERSION=E.EXGVERSION ORDER BY E.COPGNO
select a.* from emsexgversion a where not exists(select 1 from emsexgversion where CopGNo=a.CopGNo and ExgVersion>a.ExgVersion)
select 1 from ... 通常存在于exists中. 也可以写做 select 2 from ... 或者任何常数.
因为exists只是判断为不为null来返回true或者false 通常的写法还有 select count(1) from ... 其实等同于 select count(*) from ... 或者 select count(主键) from ... 这样的SQL
根据主键及索引的建立方式,速度上有细小的差别.
而-1 = -1这样的表达式是永远返回true的. 就像 1 = 2是永远返回false一样.
我们通常copy表结构就经常用 create table newtable as select * from oldtable where 1 = 2;