3,491
社区成员
发帖
与我相关
我的任务
分享
SELECT PRODUCTINF0_.FNAME AS FNAME18_0_,
PRODUCTPHO1_.CODE AS CODE19_1_,
PRODUCTBAS2_.CODE AS CODE20_2_,
PRODUCTINF0_.CODE AS CODE18_0_,
PRODUCTINF0_.ITIME AS ITIME18_0_,
PRODUCTINF0_.PTIME AS PTIME18_0_,
PRODUCTINF0_.LENGTH AS LENGTH18_0_,
PRODUCTPHO1_.WMFNAME AS WMFNAME19_1_,
PRODUCTPHO1_.BIGWIDTH AS BIGWIDTH19_1_,
PRODUCTPHO1_.BIGHEIGHT AS BIGHEIGHT19_1_,
PRODUCTPHO1_.BIGPATH AS BIGPATH19_1_,
PRODUCTPHO1_.MIDDLEWIDTH AS MIDDLEWI6_19_1_,
PRODUCTPHO1_.MIDDLEHEIGHT AS MIDDLEHE7_19_1_,
PRODUCTPHO1_.MIDDLEPATH AS MIDDLEPATH19_1_,
PRODUCTPHO1_.SMALLWIDTH AS SMALLWIDTH19_1_,
PRODUCTPHO1_.SMALLHEIGHT AS SMALLHE10_19_1_,
PRODUCTPHO1_.SMALLPATH AS SMALLPATH19_1_,
PRODUCTBAS2_.NAME AS NAME20_2_,
PRODUCTBAS2_.CATEGORY AS CATEGORY20_2_,
PRODUCTBAS2_.CCCC AS CCCC20_2_,
PRODUCTBAS2_.DESCRIBE AS DESCRIBE20_2_,
PRODUCTBAS2_.GENERATE AS GENERATE20_2_,
PRODUCTBAS2_.ECO AS ECO20_2_,
PRODUCTBAS2_.AREA AS AREA20_2_,
PRODUCTBAS2_.PL AS PL20_2_,
PRODUCTBAS2_.IMAGE AS IMAGE20_2_,
PRODUCTBAS2_.FILETYPE AS FILETYPE20_2_,
PRODUCTBAS2_.VTI AS VTI20_2_,
PRODUCTBAS2_.INTERVAL AS INTERVAL20_2_,
PRODUCTBAS2_.WWW_PATH AS WWW14_20_2_,
PRODUCTBAS2_.PORTAL_CHANNEL AS PORTAL15_20_2_
FROM PRODUCT_INFO PRODUCTINF0_,
DICTIONARY.PRODUCT_PHOTOINFO PRODUCTPHO1_,
DICTIONARY.PRODUCT_BASEINFO PRODUCTBAS2_
WHERE PRODUCTINF0_.CODE = PRODUCTPHO1_.CODE
AND PRODUCTBAS2_.CODE = PRODUCTPHO1_.CODE
AND not exists (SELECT 1
FROM PRODUCT_INFO PRODUCTINF3_
WHERE 1 = 1
AND PRODUCTINF3_.PTIME >=
TO_DATE('2010-04-01', 'yyyy-mm-dd hh24:mi:ss')
AND PRODUCTINF3_.PTIME <=
TO_DATE('2010-09-07', 'yyyy-mm-dd hh24:mi:ss')
and PRODUCTINF3_.CODE = PRODUCTINF0_.CODE
and PRODUCTINF3_.FNAME > PRODUCTINF0_.FNAME
)
AND PRODUCTINF0_.PTIME >= TO_DATE('2010-04-01', 'yyyy-mm-dd hh24:mi:ss')
AND PRODUCTINF0_.PTIME <= TO_DATE('2010-09-07', 'yyyy-mm-dd hh24:mi:ss')
SELECT * FROM (SELECT PRODUCTINF0_.FNAME AS FNAME18_0_,
PRODUCTPHO1_.CODE AS CODE19_1_,
PRODUCTBAS2_.CODE AS CODE20_2_,
PRODUCTINF0_.CODE AS CODE18_0_,
PRODUCTINF0_.ITIME AS ITIME18_0_,
PRODUCTINF0_.PTIME AS PTIME18_0_,
PRODUCTINF0_.LENGTH AS LENGTH18_0_,
PRODUCTPHO1_.WMFNAME AS WMFNAME19_1_,
PRODUCTPHO1_.BIGWIDTH AS BIGWIDTH19_1_,
PRODUCTPHO1_.BIGHEIGHT AS BIGHEIGHT19_1_,
PRODUCTPHO1_.BIGPATH AS BIGPATH19_1_,
PRODUCTPHO1_.MIDDLEWIDTH AS MIDDLEWI6_19_1_,
PRODUCTPHO1_.MIDDLEHEIGHT AS MIDDLEHE7_19_1_,
PRODUCTPHO1_.MIDDLEPATH AS MIDDLEPATH19_1_,
PRODUCTPHO1_.SMALLWIDTH AS SMALLWIDTH19_1_,
PRODUCTPHO1_.SMALLHEIGHT AS SMALLHE10_19_1_,
PRODUCTPHO1_.SMALLPATH AS SMALLPATH19_1_,
PRODUCTBAS2_.NAME AS NAME20_2_,
PRODUCTBAS2_.CATEGORY AS CATEGORY20_2_,
PRODUCTBAS2_.CCCC AS CCCC20_2_,
PRODUCTBAS2_.DESCRIBE AS DESCRIBE20_2_,
PRODUCTBAS2_.GENERATE AS GENERATE20_2_,
PRODUCTBAS2_.ECO AS ECO20_2_,
PRODUCTBAS2_.AREA AS AREA20_2_,
PRODUCTBAS2_.PL AS PL20_2_,
PRODUCTBAS2_.IMAGE AS IMAGE20_2_,
PRODUCTBAS2_.FILETYPE AS FILETYPE20_2_,
PRODUCTBAS2_.VTI AS VTI20_2_,
PRODUCTBAS2_.INTERVAL AS INTERVAL20_2_,
PRODUCTBAS2_.WWW_PATH AS WWW14_20_2_,
PRODUCTBAS2_.PORTAL_CHANNEL AS PORTAL15_20_2_
FROM PRODUCT_INFO PRODUCTINF0_,
DICTIONARY.PRODUCT_PHOTOINFO PRODUCTPHO1_,
DICTIONARY.PRODUCT_BASEINFO PRODUCTBAS2_,
RANK() over(partition by PRODUCTINF0_.code order by PRODUCTINF0_.FNAME ) as rn
WHERE PRODUCTINF0_.CODE = PRODUCTPHO1_.CODE
AND PRODUCTBAS2_.CODE = PRODUCTPHO1_.CODE
AND PRODUCTINF0_.PTIME >= TO_DATE('2010-04-01', 'yyyy-mm-dd hh24:mi:ss')
AND PRODUCTINF0_.PTIME <= TO_DATE('2010-09-07', 'yyyy-mm-dd hh24:mi:ss')
) TEMP WHERE TEMP.RN = 1
SELECT COUNT(1)
FROM PRODUCT_INFO a,
DICTIONARY.PRODUCT_PHOTOINFO b,
DICTIONARY.PRODUCT_BASEINFO c
WHERE a.CODE =b.CODE
AND c.CODE = b.CODE
AND exists (SELECT 1
FROM PRODUCT_INFO t
WHERE a.code=t.code and a.FNAME<b.FNAME)
AND a.PTIME >= TO_DATE('2010-04-01', 'yyyy-mm-dd hh24:mi:ss')
AND a.PTIME <= TO_DATE('2010-09-07', 'yyyy-mm-dd hh24:mi:ss')
--你自己在建个索引看
SELECT *
FROM (SELECT PRODUCTINF0_.FNAME AS FNAME18_0_,
PRODUCTPHO1_.CODE AS CODE19_1_,
PRODUCTBAS2_.CODE AS CODE20_2_,
PRODUCTINF0_.CODE AS CODE18_0_,
PRODUCTINF0_.ITIME AS ITIME18_0_,
PRODUCTINF0_.PTIME AS PTIME18_0_,
PRODUCTINF0_.LENGTH AS LENGTH18_0_,
PRODUCTPHO1_.WMFNAME AS WMFNAME19_1_,
PRODUCTPHO1_.BIGWIDTH AS BIGWIDTH19_1_,
PRODUCTPHO1_.BIGHEIGHT AS BIGHEIGHT19_1_,
PRODUCTPHO1_.BIGPATH AS BIGPATH19_1_,
PRODUCTPHO1_.MIDDLEWIDTH AS MIDDLEWI6_19_1_,
PRODUCTPHO1_.MIDDLEHEIGHT AS MIDDLEHE7_19_1_,
PRODUCTPHO1_.MIDDLEPATH AS MIDDLEPATH19_1_,
PRODUCTPHO1_.SMALLWIDTH AS SMALLWIDTH19_1_,
PRODUCTPHO1_.SMALLHEIGHT AS SMALLHE10_19_1_,
PRODUCTPHO1_.SMALLPATH AS SMALLPATH19_1_,
PRODUCTBAS2_.NAME AS NAME20_2_,
PRODUCTBAS2_.CATEGORY AS CATEGORY20_2_,
PRODUCTBAS2_.CCCC AS CCCC20_2_,
PRODUCTBAS2_.DESCRIBE AS DESCRIBE20_2_,
PRODUCTBAS2_.GENERATE AS GENERATE20_2_,
PRODUCTBAS2_.ECO AS ECO20_2_,
PRODUCTBAS2_.AREA AS AREA20_2_,
PRODUCTBAS2_.PL AS PL20_2_,
PRODUCTBAS2_.IMAGE AS IMAGE20_2_,
PRODUCTBAS2_.FILETYPE AS FILETYPE20_2_,
PRODUCTBAS2_.VTI AS VTI20_2_,
PRODUCTBAS2_.INTERVAL AS INTERVAL20_2_,
PRODUCTBAS2_.WWW_PATH AS WWW14_20_2_,
PRODUCTBAS2_.PORTAL_CHANNEL AS PORTAL15_20_2_
FROM PRODUCT_INFO PRODUCTINF0_,
DICTIONARY.PRODUCT_PHOTOINFO PRODUCTPHO1_,
DICTIONARY.PRODUCT_BASEINFO PRODUCTBAS2_,
RANK() over(partition by PRODUCTINF0_.code order by PRODUCTINF0_.FNAME) as rn
WHERE PRODUCTINF0_.CODE = PRODUCTPHO1_.CODE
AND PRODUCTBAS2_.CODE = PRODUCTPHO1_.CODE
AND PRODUCTINF0_.PTIME >=
TO_DATE('2010-04-01', 'yyyy-mm-dd hh24:mi:ss')
AND PRODUCTINF0_.PTIME <=
TO_DATE('2010-09-07', 'yyyy-mm-dd hh24:mi:ss')) TEMP
WHERE TEMP.RN = 1
;