mysql - 如何在 cron 作业中运行复杂的 mysql 查询？

Question

我想仅在 M T W Th F 早晨、debian、mysql 服务器上运行此查询。

SET @FromDate='2014-01-01' , @ToDate='2014-05-01';
SELECT a.PatNum, inssub.SubscriberID, p.BirthDate, DATE(a.AptDateTime)AS  
'DateApt' /* ,Date_Format(a.AptDateTime,'%H:%m') AS 'TimeApt'*/
/*,a.ProcDescript*/, SUM(pl.ProcFee) AS $TotFee/*,b.TimePeriod,      
b.MonetaryAmt AS $AnnualMax, ip.monthrenew*/
,ca.Phone AS 'CarrierPh', IFNULL(ca.CarrierName, 'X - No Insurance') AS  
Carrier,
(CASE WHEN b.TimePeriod=1 AND ip.MonthRenew<=MONTH(CURDATE()) AND  
IFNULL(ip.MonthRenew,0)>0 THEN STR_TO_DATE(CONCAT(YEAR(CURDATE()),  
'-',ip.MonthRenew,'-01'),'%Y-%m-%d')
WHEN b.TimePeriod=1 AND ip.MonthRenew>MONTH(CURDATE()) AND  \  
IFNULL(ip.MonthRenew,13)<13 THEN STR_TO_DATE(CONCAT(YEAR(CURDATE())-1,  
'-',ip.MonthRenew,'-01'),'%Y-%m-%d')
WHEN b.TimePeriod=2 THEN STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-01-01'),'
%Y-%m-%d')
WHEN NOT ISNULL(ca.CarrierName) THEN STR_TO_DATE(CONCAT(YEAR(CURDATE()),  
'-01-01'),'%Y-%m-%d') END) AS DateRenew/*when coverage starts*/
,b.MonetaryAmt/*AnnualMax*/-(SELECT SUM(IFNULL(claimproc.InsPayAmt,0))
FROM claimproc
WHERE claimproc.Status IN (1, 3, 4)
AND claimproc.PatNum=p.PatNum
AND claimproc.PlanNum=inssub.PlanNum
AND DATE(claimproc.ProcDate) BETWEEN DateRenew AND CURDATE())/*AmtUsed*/ 
AS $InsLeft
/*,(SELECT SUM(IFNULL(claimproc.InsPayAmt,0))
FROM claimproc
WHERE claimproc.Status IN (1, 3, 4)
AND claimproc.PatNum=p.PatNum
AND claimproc.PlanNum=inssub.PlanNum
AND DATE(claimproc.ProcDate) BETWEEN DateStart AND CURDATE()) AS    
$AmtUsed*/
FROM patient p
INNER JOIN appointment a ON a.PatNum=p.PatNum AND  
a.AptStatus=1
LEFT JOIN patplan pp ON p.PatNum=pp.PatNum
LEFT JOIN inssub ON inssub.InsSubNum=pp.InsSubNum
LEFT JOIN insplan ip ON ip.PlanNum=inssub.PlanNum
LEFT JOIN carrier ca ON ip.CarrierNum=ca.CarrierNum
LEFT JOIN procedurelog pl ON pl.AptNum=a.AptNum
LEFT JOIN benefit b ON b.PlanNum=inssub.PlanNum AND b.BenefitType=5 
AND b.TimePeriod IN(1,2)
LEFT JOIN covcat c ON b.CovCatNum=c.CovCatNum AND c.EbenefitCat=1  
WHERE (DATE(a.AptDateTime) BETWEEN @FromDate AND @ToDate)
GROUP BY a.AptNum, inssub.PlanNum
ORDER BY Carrier, p.LName,p.FName;

此外，输出将自动打印出来...我如何将此代码嵌套到 bash 脚本中？

Answer 1

crontab 中的普通 mysql -user -host -password -in "QUERY HERE"应该可以为您完成此操作。