1.左关联一个新的子表
-- 全选出库申请单:
SELECT
ap.QUARTER_ID , ap.OUT_APPLY_ID , ap.APPLY_TYPE , ap.REGION_ID , ap.REGION_NAME , ap.RES_TYPE ,
ac.ACTIVITY_BEGIN_DATE , ac.ACTIVITY_END_DATE , ac.APPLY_TIME , ap.FILL_USER_NAME , ap.STATUS ,
FROM out_apply ap
LEFT JOIN activity ac
ON ap.ACTIVITY_ID = ac.ACTIVITY_ID
LEFT JOIN (select TIMESTAMPDIFF(DAY,ACTIVITY_BEGIN_DATE,NOW()) AS mdate , acc.ACTIVITY_ID FROM activity acc) ab
on ac.ACTIVITY_ID = ab.ACTIVITY_ID
WHERE 1 = 1
and ap.REGION_ID = '1'
AND ap.RES_TYPE = '1'
AND ab.mdate = '331'
AND ap.APPLY_TYPE = '1'
AND ap.QUARTER_ID = '1'
AND ap.OUT_APPLY_NO = '1'
ORDER BY ap.ACTIVITY_ID;
子查询:
select TIMESTAMPDIFF(DAY,ACTIVITY_BEGIN_DATE,NOW()) FROM activity;
2.直接在里面查询
写成TIMESTAMPDIFF(DAY,NOW(),ac.ACTIVITY_BEGIN_DATE) AS CLOSE_DATE ,函数的形式
但是在AND条件里不能用 CLOSE_DATE 这个属性 , 因为查询的过程中函数还在运行 , 可能查不到 , 所以AND条件里还需要再去使用 TIMESTAMPDIFF(DAY,NOW(),ac.ACTIVITY_BEGIN_DATE) = ' ' 这个条件
SELECT
ap.QUARTER_ID , ap.OUT_APPLY_ID , ap.APPLY_TYPE , ap.REGION_ID , ap.REGION_NAME , ap.RES_TYPE ,
ac.ACTIVITY_BEGIN_DATE , ac.ACTIVITY_END_DATE , ac.APPLY_TIME , ap.FILL_USER_NAME , ap.STATUS ,
TIMESTAMPDIFF(DAY,NOW(),ac.ACTIVITY_BEGIN_DATE) AS CLOSE_DATE
FROM out_apply ap
LEFT JOIN activity ac
ON ap.ACTIVITY_ID = ac.ACTIVITY_ID
WHERE 1 = 1
and ap.REGION_ID = '1'
AND ap.RES_TYPE = '1'
AND TIMESTAMPDIFF(DAY,NOW(),ac.ACTIVITY_BEGIN_DATE) = ''
AND ap.APPLY_TYPE = '1'
AND ap.QUARTER_ID = '1'
AND ap.OUT_APPLY_NO = '1'
ORDER BY ap.ACTIVITY_ID;
