我遇到一种情况,我们正在计算项目的付款状态。
";CASE";子句用于查找支付状态
现在我需要在SQL中对CASE语句的计算结果进行过滤,例如如果";付费的";在过滤器中被选择,而我们只需要看到那些支付状态为"0"的记录;付费";。
我知道我们不能在WHERE子句中使用别名。
在我的情况下,我添加了一个别名";pm_clause";在WHERE子句中,但添加到以下SQL中以供参考。
有没有一种方法可以在SQL中应用这样的过滤器?
SELECT DISTINCT rs.id AS rs_id
, SUM(rs.amount_payable) AS revenue
, SUM(rs.amount_recieved) AS receipt
, GROUP_CONCAT(rs.payment_status) AS payment_status
, s.id AS s_id
, s.file_number
, s.company_id
, s.travel_start_date
, s.travel_region
, s.country
, s.lead_name
, c.id
, c.name AS company
, c.currency_sign
, c.currency_code
, CASE WHEN SUM(rs.amount_recieved) = SUM(rs.amount_payable)
THEN "Paid"
WHEN SUM(rs.amount_recieved) = 0
THEN "Not Paid"
WHEN SUM(rs.amount_recieved) > 0 AND SUM(rs.amount_recieved) < SUM(rs.amount_payable)
THEN "Part paid"
WHEN SUM(rs.amount_recieved) > SUM(rs.amount_payable)
THEN "Overpaid"
ELSE "Error" END AS pm_status
FROM erp_revenue_schedule rs
JOIN erp_sales s
ON s.id = rs.sales_id
JOIN erp_company c
ON c.id = s.company_id
WHERE rs.is_active = 1
AND s.is_active = 1
AND c.is_active = 1
AND pm_status = 'Not paid'
ORDER
BY rs.id asc
LIMIT 0 ,10
当select中有普通列和聚合函数时,需要使用GROUP BY
,而不是DISTINCT
。
此外,如果您只想要具有SUM(rs.amount_recieved) = 0
的行,则根本不清楚为什么需要CASE
语句。具有聚合函数的条件进入HAVING
-子句,而不是WHERE
子句。