select count(DISTINCT t0.A0100) from A001A001 t0,H001A001 t1
where 1=1 and t1.A0107='1' and t1.A0100=t0.A0100
and t1.INUMBER in(select t1.INUMBER
from H001A001 t1, A001A001 t0
where t1.A0100=t0.A0100
and t1.NEWDATETIME<=to_date('2004-04-23 18:45:00','yyyy-mm-dd hh24:mi:ss') and rownum < 2)
where 1=1 and t1.A0107='1' and t1.A0100=t0.A0100
and t1.INUMBER in(select t1.INUMBER
from H001A001 t1, A001A001 t0
where t1.A0100=t0.A0100
and t1.NEWDATETIME<=to_date('2004-04-23 18:45:00','yyyy-mm-dd hh24:mi:ss') and rownum < 2)
select count(DISTINCT t0.A0100) from A001A001 t0,H001A001 t1
where 1=1 and t1.A0107='1' and t1.A0100=t0.A0100
and t1.INUMBER in(select t1.INUMBER
from H001A001 t1, A001A001 t0
where t1.A0100=t0.A0100
and t1.NEWDATETIME<=to_date('2004-04-23 18:45:00','yyyy-mm-dd hh24:mi:ss') )进行转换!
如果要根据
select t0.A0100 from A001A001 t0,H001A001 t1
where 1=1 and t1.A0107='1'
and t1.INUMBER=(select top 1 INUMBER from H001A001 g0
where g0.A0100=t0.A0100
and g0.NEWDATETIME<='2004-04-23 18:45:00'
order by g0.NEWDATETIME desc)
那么要复杂一点!
select t0.A0100 from A001A001 t0,H001A001 t1
where 1=1 and t1.A0107='1'
and t1.INUMBER=(select INUMBER from (select INUMBER from H001A001 g0
where g0.A0100=t0.A0100
and g0.NEWDATETIME<=to_date('2004-04-23 18:45:00','yyyymmdd')
order by g0.NEWDATETIME desc) where rownum < 2);
where 1=1 and t1.A0107='1'
and t1.INUMBER=(select INUMBER from H001A001 g0
where g0.A0100=t0.A0100
and g0.NEWDATETIME<=to_date('2004-04-23 18:45:00','yyyy-mm-dd hh24:mi:ss') and rownum=1)--不知道你第一where后为什么要有一个1=1?
oracle 好像不能解析外面的 t0
where 1=1 and t1.A0107='1'
and t1.INUMBER=(select INUMBER from (select INUMBER from H001A001 g0
where g0.A0100=t0.A0100
and g0.NEWDATETIME<=to_date('2004-04-23 18:45:00','yyyy-mm-dd hh24:mi:ss')
order by g0.NEWDATETIME desc) where rownum=1);
FROM A001A001 T0, H001A001 T1
WHERE 1 = 1
AND T1.A0107 = '1'
AND T1.INUMBER =
SELECT *
FROM (SELECT GO.*,
RANK() OVER(ORDER BY G0.NEWDATETIME DESC) RK
FROM H001A001 G0
WHERE G0.A0100 = T0.A0100
AND G0.NEWDATETIME <= '2004-04-23 18:45:00')
WHERE RK = 1
FROM A001A001 T0, H001A001 T1
WHERE 1 = 1
AND T1.A0107 = '1'
AND T1.INUMBER =
SELECT INUMBER
FROM (SELECT GO.INUMBER INUMBER,
RANK() OVER(ORDER BY G0.NEWDATETIME DESC) RK
FROM H001A001 G0
WHERE G0.A0100 = T0.A0100
AND G0.NEWDATETIME <= '2004-04-23 18:45:00')
WHERE RK = 1
where 1=1 and t0.number=t1.number
where 1=1 and t0.number=t1.number
前面的少了对'()'