php - MAX( ) WHERE - 错误

Question

我有一个选择语句(工作)

 Select * FROM 
    (SELECT id, type, color, product,  
    SUM(Quantity) AS TotalQuantity, 
    MAX(Date) AS LatestDate 
    FROM inventory 
    GROUP BY id, color, type) 
    AS alias 
    WHERE TotalQuantity > 0

我正在尝试向 Max(Date) 添加 WHERE 子句，但我没有任何运气:

我想我只需将其更改为:MAX(Date) AS LateDate WHERE stock = 1但这不起作用。

不工作:

 Select * FROM 
    (SELECT id, type, color, product,  
    SUM(Quantity) AS TotalQuantity, 
/* Problem Here */ MAX(Date) AS LatestDate WHERE stock = 1
    FROM inventory 
    GROUP BY id, color, type) 
    AS alias 
    WHERE TotalQuantity > 0

Answer 1

MAX(IF(stock = 1, Date, NULL)) AS LatestDate

它将查找 stock = 1 的最大日期，如果没有此类行，则为 null