周报kettle开发记录

SELECT A FROM 表 WHERE TIME >=DATE_SUB(CURDATE(),INTERVAL 1 DAY) AND TIME <= CURDATE

 SELECT t.para_code,t.para_desc,t.para_subject,t.para_type FROM xm_para_agree t WHERE t.para_code='xm_status'
 
 
 SELECT* FROM xm_main WHERE xm_code IS NOT NULL AND xm_status NOT IN ('项目完成','项目冻结') AND delete_status<>'1' OR delete_status IS NULL
 
 
 SELECT * FROM xm_agree_zb t1  WHERE input_date >=DATE_SUB(NOW(),INTERVAL 7 DAY) AND input_date <= NOW()
 
 SELECT COUNT(1),a.xm_Code FROM 
 (SELECT *  FROM xm_main  t2
 LEFT JOIN   xm_agree_zb t1
 ON t2.xm_code =t1.xm_code 
 WHERE t2.xm_code IS NOT NULL AND t2.xm_status NOT IN ('项目完成','项目冻结') AND (t2.delete_status<>'1' OR t2.delete_status IS NULL)
 AND (t1.input_date <=DATE_SUB(NOW(),INTERVAL 8 DAY) OR t1.input_date IS NULL))a
 GROUP BY a.xm_code


SELECT * FROM 
(SELECT COUNT(1) count1,a.xm_code FROM 
 (SELECT t2.xm_code,t2.xm_desc  FROM xm_main  t2
 LEFT JOIN   xm_agree_zb t1
 ON t2.xm_code =t1.xm_code 
 WHERE t2.xm_code IS NOT NULL AND t2.xm_status NOT IN ('项目完成','项目冻结') AND (t2.delete_status<>'1' OR t2.delete_status IS NULL)
 AND t1.input_date >=DATE_SUB(NOW(),INTERVAL 8 DAY) AND t1.input_date <= NOW())a
 GROUP BY a.xm_code) b
 WHERE count1<1
  
 SELECT DISTINCT xm_contact,xm_general FROM xm_main WHERE xm_code IS NOT NULL
  
SELECT DISTINCT t2.xm_code,t2.xm_desc,t1.xm_code  FROM xm_main  t2
 LEFT JOIN   xm_agree_zb t1
 ON t2.xm_code =t1.xm_code 
 WHERE t2.xm_code IS NOT NULL AND t2.xm_status NOT IN ('项目完成','项目冻结') AND (t2.delete_status<>'1' OR t2.delete_status IS NULL)
 AND (t1.input_date <=DATE_SUB(NOW(),INTERVAL 8 DAY) OR t1.input_date IS NULL)  
 
 
 
 SELECT t.USER_EMAIL mail_address   FROM portal.t_acl_userinfo AS t  WHERE t.username IN
(SELECT DISTINCT dt_lxr a FROM xm_agree_main WHERE dt_htbh='${dt_htbh}') 
AND t.user_email IS NOT NULL


SELECT DISTINCT t1.xm_contact  FROM xm_main t1 WHERE t1.xm_code='201803WH-QYYH0101'
UNION 
SELECT DISTINCT t2.xm_general FROM  xm_main t2 WHERE t2.xm_code='201803WH-QYYH0101'