时间戳

首页 > 解决方案 > MySQL：根据账单日期过去多少天显示汇总会费

mysql - MySQL：根据账单日期过去多少天显示汇总会费

问题描述

我想要达到什么目的？
预期结果屏幕截图说明

：了解自账单日期过去以来应支付的金额。这里 0-15 表示账单日期已过 15 天，16-30 表示账单日期已过 16 至 30 天，我想知道这些范围内的到期金额和最后的总计。

涉及的表数： 1
表：用户表
相关列：
bill_date
pending_amount

我使用的查询：

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

我得到的结果：我得到的
当前结果

如何达到结果？

标签： mysqlcase

解决方案

您的 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`

推荐阅读