sql改正

统计当日的应还金额

原SQL： 在里面过滤4个有问题的借据

-- 统计备位截止当日的应还 
SELECT sum(if(fee_type = 'Pricinpal',bill_amount,0)) as `当日应还本金（备）` 
	,sum(if(fee_type = 'Interest',bill_amount,0)) as `当日应还利息（备）` 
	,sum(if(fee_type = 'Penalty',bill_amount,0)) as `当日应还罚息（备）` 
from 
	(select due_bill_no,fee_type,bill_amount ,bill_status ,bill_date ,repaid_date ,term  
	from acc_cus.cus_bill_detail 
	where  
	-- 统计逾期未还的数据 
	(project_no = 'BUWS0014200001' and bill_status = 'OVERDUE' AND bill_date < '2022-10-01')  
	-- 统计当天出账的数据，包括当天到期、提前结清的数据 
	or (project_no = 'BUWS0014200001' and bill_date = '2022-10-01') 
	-- 统计当天还款的以前逾期的数据 
	or (project_no = 'BUWS0014200001' AND bill_date < '2022-10-01' AND repaid_date = '2022-10-01' and bill_status = 'REPAID') 
	HAVING due_bill_no not in --剔除4个有问题的借据('1121072203075770893065','1121072209002313690366','1121072516444084110457','1121091914192720237495') 
);  

改正后的SQL：

SELECT 
    SUM(CASE WHEN fee_type = 'Pricinpal' THEN bill_amount ELSE 0 END) AS  `当日应还本金（备）` ,
    SUM(CASE WHEN fee_type = 'Interest' THEN bill_amount ELSE 0 END) AS  `当日应还利息（备）` ,
    SUM(CASE WHEN fee_type = 'Penalty' THEN bill_amount ELSE 0 END) AS  `当日应还罚息（备）` 
FROM 
    (SELECT 
        due_bill_no, fee_type, bill_amount, bill_status, bill_date, repaid_date, term 
    FROM 
        acc_cus.cus_bill_detail
    WHERE 
        -- 统计逾期未还的数据
        (project_no = 'BUWS0014200001' AND bill_status = 'OVERDUE' AND bill_date < '2022-10-01') 
        -- 统计当天出账的数据，包括当天到期、提前结清的数据
        OR (project_no = 'BUWS0014200001' AND bill_date = '2022-10-01')
        -- 统计当天还款的以前逾期的数据
        OR (project_no = 'BUWS0014200001' AND bill_date < '2022-10-01' AND repaid_date = '2022-10-01' AND bill_status = 'REPAID')
    ) AS t
WHERE 
    due_bill_no NOT IN ('1121072203075770893065', '1121072209002313690366', '1121072516444084110457', '1121091914192720237495'); 
 

修改说明：

1. 在SELECT语句中，使用了SUM和CASE WHEN语句来计算应还本金、利息和罚息的总和，避免了使用IF函数的错误。
2. 在FROM子句中，将子查询的结果作为表t来使用，使得查询更加清晰。
3. 在WHERE子句中，将NOT IN语句放在外层查询中，避免了在子查询中使用HAVING语句的错误。