MySQL:显示自帐单日期过去多少天以来的总会费

Question

我想要实现什么目标？
Expected Result Screenshot

说明:了解自账单日期过去以来应付金额的概览。这里 0-15 表示自账单日期过去 15 天，16-30 表示自账单日期过去 16 到 30 天，我想知道这些范围内的应付金额以及最后的总计金额。

没有。涉及表数:1
表:用户表
相关栏目:
帐单日期
待处理金额

我使用的查询:

SELECT (CASE 
        WHEN DATEDIFF(NOW(), bill_date) BETWEEN 0 AND 15 THEN '0-15'
        WHEN DATEDIFF(NOW(), bill_date) BETWEEN 16 AND 30 THEN '16-30'  
        WHEN DATEDIFF(NOW(), bill_date) BETWEEN 31 AND 45 THEN '31-45'
        WHEN DATEDIFF(NOW(), bill_date) BETWEEN 46 AND 60 THEN '46-60'
        WHEN DATEDIFF(NOW(), bill_date) BETWEEN 61 AND 90 THEN '61-90'
        WHEN DATEDIFF(NOW(), bill_date) BETWEEN 91 AND 180 THEN '91-180' 
        WHEN DATEDIFF(NOW(), bill_date) BETWEEN 181 AND 365 THEN '181-365'
        WHEN DATEDIFF(NOW(), bill_date) >= 366 THEN '> 365
            END
        ) as bill_range,
        SUM(pending_amount) as amount_due,
FROM 
    `user`
    GROUP BY bill_range

我得到的结果:
Current result I got

如何实现结果？

Answer 1

您的 CASE 语句仅创建 1 列。
您需要条件聚合:

SELECT  
  SUM(CASE WHEN DATEDIFF(NOW(), bill_date) BETWEEN 0 AND 15 THEN pending_amount ELSE 0 END) `0-15`,
  SUM(CASE WHEN DATEDIFF(NOW(), bill_date) BETWEEN 16 AND 30 THEN pending_amount ELSE 0 END) `16-30`,  
  SUM(CASE WHEN DATEDIFF(NOW(), bill_date) BETWEEN 31 AND 45 THEN pending_amount ELSE 0 END) `31-45`,
  SUM(CASE WHEN DATEDIFF(NOW(), bill_date) BETWEEN 46 AND 60 THEN pending_amount ELSE 0 END) `46-60`,
  SUM(CASE WHEN DATEDIFF(NOW(), bill_date) BETWEEN 61 AND 90 THEN pending_amount ELSE 0 END) `61-90`,
  SUM(CASE WHEN DATEDIFF(NOW(), bill_date) BETWEEN 91 AND 180 THEN pending_amount ELSE 0 END) `91-180`, 
  SUM(CASE WHEN DATEDIFF(NOW(), bill_date) BETWEEN 181 AND 365 THEN pending_amount ELSE 0 END) `181-365`,
  SUM(CASE WHEN DATEDIFF(NOW(), bill_date) >= 366 THEN pending_amount ELSE 0 END) `> 365`,
  SUM(pending_amount) `grand total`
FROM `user`