代码之家  ›  专栏  ›  技术社区  ›  Marvin34565

在MySQL视图中左键连接

  •  0
  • Marvin34565  · 技术社区  · 2 年前

    我有两个表,一个跟踪产品的购买(用EAN表示),另一个跟踪产品的销售。但并非所有销售的产品都在采购表中,反之亦然。例如:

    购买

    PurchaseNo 伊恩
    1.
    2. 0002
    3. 0003
    4. 0004

    出售

    萨勒诺 伊恩
    1. 0002
    2. 0003
    3. 0004
    4. 0005

    我还有一张表格,上面有EAN编号的产品规格:

    ProductEAN

    伊恩 名称
    0001 产品1
    0002 产品2
    0003 产品3
    0004 产品4
    0005 产品5

    现在我想创建一个视图,告诉我当前的库存(即使库存为负数)。我目前的方法是为购买和销售创建一个视图,每个视图中的“金额”列告诉我购买或销售了多少产品。

    因此,对于我的购买,这将是:

    CREATE VIEW `PurchaseAmount` AS
    SELECT 
    `ProductEAN`.`EAN` AS `EAN`,
    COUNT(`Purchase`.`EAN`) AS `Amount`
    FROM (`ProductEAN` JOIN `Purchase`)
    WHERE `ProductEAN`.`EAN` = `Purchase`.`EAN`
    GROUP BY `ProductEAN`.`EAN`
    

    和我的销售额相当。

    现在我想结合这两个视图来创建最终的库存视图。问题是,据我所知和测试,我只能在我的视图中进行正常连接,这导致我的两个“子”视图不包含未购买或出售的EAN。因此,我的最终库存视图只能显示采购和销售表中的EAN。

    有没有办法在MySQL视图中进行左连接,从而只保留所有EAN,即使这些EAN没有在视图引用的表中使用。

    1 回复  |  直到 2 年前
        1
  •  2
  •   lemon    2 年前

    你可以先计算 COUNT 分别购买和销售,然后离开 LEFT JOIN 作为使查询更高效的最后一个操作。

    SELECT 
        ProductEAN.EAN                          AS EAN,
        COALESCE(Purchases.num_purchases, 0)    AS num_purchases,
        COALESCE(Sales.num_sales, 0)            AS num_sales
    FROM 
        ProductEAN
    LEFT JOIN 
        (
        SELECT 
            EAN,
            COUNT(EAN) AS num_purchases
        FROM 
            Purchase
        GROUP BY 
            EAN
        ) Purchases
    ON 
        ProductEAN.EAN = Purchases.EAN
    LEFT JOIN 
        (
        SELECT 
            EAN,
            COUNT(EAN) AS num_sales
        FROM 
            Sale
        GROUP BY 
            EAN
        ) Sales
    ON 
        ProductEAN.EAN = Sales.EAN
    

    因此,您可以将此代码存储在视图中。

    请参阅此小提琴进行测试: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b2d777f367960cf90d6539980c2ad935 .