时间戳

首页 > 解决方案 > Mysql 计算多个 SUM() 变量

mysql - Mysql 计算多个 SUM() 变量

问题描述

我想计算 2 sum() 我已经尝试过如下查询，但它工作得非常慢。有什么想法可以让它变得更好吗？

SELECT customer, 
       SUM(CASE WHEN book_day BETWEEN '2020-01-01' AND '2020-01-31'  
                THEN pax+free 
                ELSE 0 
                END) AS January,
       SUM(CASE WHEN book_day BETWEEN '2020-02-01' AND '2020-02-31'  
                THEN pax+free 
                ELSE 0 
                END) AS February,
       ( SUM(CASE WHEN book_day BETWEEN '2020-01-01' AND '2020-01-31'  
                  THEN pax+free 
                  ELSE 0 
                  END) + 
         SUM(CASE WHEN book_day BETWEEN '2020-02-01' AND '2020-02-31'  
                  THEN pax+free 
                  ELSE 0 
                  END) ) AS total
 FROM rezervations

我怎样才能变得更简单January + February as total

标签： mysqlsqlsumquery-performancecalculated-columns

解决方案

保留BETWEEN '2020-01-01' AND '2020-02-29'在 where 子句中。

确保 book_day 列上有索引

SELECT customer, 
SUM(CASE WHEN book_day BETWEEN '2020-01-01' AND '2020-01-31' THEN pax+free ELSE 0 END) as January,
SUM(CASE WHEN book_day BETWEEN '2020-02-01' AND '2020-02-29'  THEN pax+free ELSE 0 END) as February,
(SUM(CASE WHEN book_day BETWEEN '2020-01-01' AND '2020-02-29'  THEN pax+free ELSE 0 END) ) as total
 FROM rezervations 
WHERE
book_day BETWEEN '2020-01-01' AND '2020-02-29'

推荐阅读