mysql - 如何在单个 mysql 查询中获取当前日期、当前星期、当前月份、当前年份销售额总和？

Question

我已经单独编写了以下查询，但我想在一个查询中，如何做到这一点，谁能帮忙..？

SELECT IFNULL(SUM(net_amount),0) as current_date FROM orders WHERE is_cancelled = false and business_date = CURRENT_DATE();
SELECT IFNULL(SUM(net_amount),0) as current_week FROM orders WHERE is_cancelled = false and YEARWEEK(business_date) = YEARWEEK(CURRENT_DATE());
SELECT IFNULL(SUM(net_amount),0) as current_month FROM orders WHERE is_cancelled = false and MONTH(business_date) = MONTH(CURRENT_DATE());
SELECT IFNULL(SUM(net_amount),0) as current_year FROM orders WHERE is_cancelled = false and YEAR(business_date) = YEAR(CURRENT_DATE());

标签： mysqlsql

Answer 1

您可以使用条件聚合将所有单独的选择语句组合成一个统一的语句，例如

SELECT SUM(CASE WHEN business_date = CURRENT_DATE() 
                THEN 
                    net_amount 
                ELSE 
                    0 
                 END ) AS current_date,
       SUM(CASE WHEN YEARWEEK(business_date) = YEARWEEK(CURRENT_DATE()) 
                THEN 
                    net_amount 
                ELSE 
                    0 
                 END ) AS current_week,
       SUM(CASE WHEN MONTH(business_date) = MONTH(CURRENT_DATE()) 
                THEN 
                    net_amount 
                ELSE 
                    0 
                 END ) AS current_month,
       SUM(CASE WHEN YEAR(business_date) = YEAR(CURRENT_DATE()) 
                THEN 
                    net_amount 
                ELSE 
                    0 
                 END ) AS current_year       
  FROM orders 
 WHERE is_cancelled = false