mysql - 结合三个(复杂的)查询

Question

我有一个查询，用于计算库存总数并显示每个库存项目的最后更新日期。我有两种不同的库存类型 0 和 1(传入、传出)。我希望能够在页面底部放置“最后收到的库存是:”和“最后发货是:”。

这是我对库存进行分组和汇总的主要查询:

 $query = "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"; 

现在我还希望能够运行这两个查询来获取“库存”0 和 1(传入、传出)的最后更新时间

$query = "SELECT MAX(Date) FROM inventory WHERE stock = 0";

$query = "SELECT MAX(Date) FROM inventory WHERE stock = 1";

结合这三个查询有什么帮助吗？我正在尝试 UNION 但运气不佳。

Answer 1

尝试这样的事情:

SELECT  alias.*,
    zeroStock.zeroDate,
    nonzeroStock.nonzeroDate
FROM
    (
        SELECT  id, 
            type, 
            color, 
            product, 
            SUM(Quantity) AS TotalQuantity, 
            MAX(Date) AS LatestDate 
        FROM    inventory 
        GROUP BY id, color, type
    ) alias INNER JOIN
    (
        SELECT id, MAX(Date) zeroDate
        FROM inventory 
        WHERE stock = 0
        GROUP BY id
    ) zeroStock on alias.id = zeroStock.id
    INNER JOIN
    (
        SELECT id, MAX(Date) nonzeroDate
        FROM inventory 
        WHERE stock = 1
        GROUP BY id
    ) nonzeroStock on alias.id = nonzeroStock.id
WHERE alias.TotalQuantity > 0