在mysql中使用if条件更改日期

Question

为此，我的查询是=

SELECT SalesDate,COUNT(Shape) as pcs,
       ROUND(SUM(TotalAmount),2) as amount,
       ROUND(SUM(Carat),2) as carat,
       ROUND(ROUND(SUM(TotalAmount),2)/ROUND(SUM(Carat),2),2) as avgprice 
from `tbl_sales`
WHERE IF((SalesDate = CURDATE() - INTERVAL 1 DAY) = null, SalesDate=CURDATE() - INTERVAL 2 DAY,SalesDate= CURDATE() - INTERVAL 1 DAY)

这就是我的回答

​

​

因此，在If条件下，我希望确保如果昨天数据中的数据为空或0，那么它将花费前天。

Answer 1

您需要将您的列转换为最新：

SELECT SalesDate,COUNT(Shape) as pcs,
       ROUND(SUM(TotalAmount),2) as amount,
       ROUND(SUM(Carat),2) as carat,
       ROUND(ROUND(SUM(TotalAmount),2)/ROUND(SUM(Carat),2),2) as avgprice 
from `tbl_sales`
WHERE IF((CAST(SalesDate AS date) = CURDATE() - INTERVAL 1 DAY), CAST(SalesDate AS date) = CURDATE() - INTERVAL 2 DAY, CAST(SalesDate AS date) = CURDATE() - INTERVAL 1 DAY)

Answer 2

可能是这样的：

SELECT SalesDate,COUNT(Shape) as pcs,
       ROUND(SUM(TotalAmount),2) as amount,
       ROUND(SUM(Carat),2) as carat,
       ROUND(ROUND(SUM(TotalAmount),2)/ROUND(SUM(Carat),2),2) as avgprice 
FROM `tbl_sales`
GROUP BY SalesDate
HAVING SalesDate = CASE WHEN (SalesDate = CURDATE() - INTERVAL 1 DAY)=0
                        THEN CURDATE() - INTERVAL 2 DAY
                        WHEN (SalesDate = CURDATE() - INTERVAL 1 DAY)=1
                           AND amount IS NULL
                        THEN CURDATE() - INTERVAL 2 DAY
                        ELSE CURDATE() - INTERVAL 1 DAY END;

当您执行(SalesDate = CURDATE() - INTERVAL 1 DAY)时，它将返回false=0和true=1。因此执行(SalesDate = CURDATE() - INTERVAL 1 DAY) = NULL，尽管它应该是.. IS NULL而不是.. = NULL。无论哪种方式，它都不会起作用。让我们检查一下HAVING部件中的CASE表达式。

1. 如果返回0，则表示没有与指定的日期匹配，则返回2天前：

CASE WHEN (SalesDate = CURDATE() - INTERVAL 1 DAY)=0 
                THEN CURDATE() - INTERVAL 2 DAY

1. 如果日期检查匹配，返回1，但返回金额为NULL，则也需要提前2天：

WHEN (SalesDate = CURDATE() - INTERVAL 1 DAY)=1  
       AND amount IS NULL
  THEN CURDATE() - INTERVAL 2 DAY

1. Else take昨日日期：

 ELSE CURDATE() - INTERVAL 1 DAY END;

Demo fiddle