1.我原来的sql语句,在有值的情况下没有问题
SELECT t1.SITE_NAME as sitename,t2.TYPE_NAME AS category_c ,
T.ID,T.NEW_PRICE,T.SITE_ID,T.DATE_SOURCE,T.INVESTIGATORS,T.IS_DEL,T.INSERT_USER,T.INSERT_DATE,
T.UPDATE_DATE,T.UPDATE_USER,T.CHANGE_DATE,T.CATEGORY,T.GAP,T.COST_PRICE,T.PROFITS,
T.ISHAVEACTIVE,T.PRICE,T.ACTIVE_NAME,T.GAP_GUAPAI,T.ACTIVITY_FORM,T.ACTIVITY_GAP,T.ALLGAP
FROM T_SITE t1
LEFT JOIN (
SELECT *
FROM T_PRICECHANGE
WHERE (site_id,CHANGE_DATE) in
(SELECT site_id,MAX(CHANGE_DATE) FROM T_PRICECHANGE GROUP BY site_id)
) t on t1."ID"=T.SITE_ID
LEFT JOIN T_OILTYPE t2 on t.category =T2.ID
where T.id is not null and t.is_del = 0 and T1.IS_DEL=0
2.后来测试完毕正式上线,由于清空了数据库,在没有数据的额情况下报了错误,需要修改
SELECT t1.SITE_NAME as sitename,t2.TYPE_NAME AS category_c ,
T.ID,T.NEW_PRICE,T.SITE_ID,T.DATE_SOURCE,T.INVESTIGATORS,T.IS_DEL,T.INSERT_USER,T.INSERT_DATE,
T.UPDATE_DATE,T.UPDATE_USER,T.CHANGE_DATE,T.CATEGORY,T.GAP,T.COST_PRICE,T.PROFITS,
T.ISHAVEACTIVE,T.PRICE,T.ACTIVE_NAME,T.GAP_GUAPAI,T.ACTIVITY_FORM,T.ACTIVITY_GAP,T.ALLGAP
FROM T_SITE t1
LEFT JOIN (
SELECT *
FROM T_PRICECHANGE
WHERE (site_id||','||CHANGE_DATE) in
(SELECT site_id||','||MAX(CHANGE_DATE) FROM T_PRICECHANGE GROUP BY site_id)
) t on t1."ID"=T.SITE_ID
LEFT JOIN T_OILTYPE t2 on t.category =T2.ID
where T.id is not null and t.is_del = 0 and T1.IS_DEL=0