时间戳

首页 > 解决方案 > MySQL：基于以后加入的表的加入条件

mysql - MySQL：基于以后加入的表的加入条件

问题描述

SELECT
    p.Sku,
    p.Barcode,
    s.AmountIncl,

    CASE
        WHEN SUM(dl.Qty) IS NULL THEN 0
        ELSE SUM(dl.Qty)
    END AS Qty,

    CASE
        WHEN SUM(dl.SubTotal) IS NULL THEN 0
        ELSE SUM(dl.SubTotal)
    END AS SubTotal

FROM
    Product AS p

        LEFT JOIN
    DocumentLine AS dl ON p.Sku = dl.Sku

        LEFT JOIN
    Document AS d ON dl.DocumentId = d.DocumentId
        AND d.DocumentTypeEnum = 'Order'
        AND d.PaymentStatusEnum = 'Paid'

        LEFT JOIN
    StandardPrice AS s ON p.ProductId = s.ProductId

WHERE
    p.Barcode IS NOT NULL

GROUP BY p.Sku

我正在尝试获取按 Sku 分组的实际已支付订单的数量和小计数字。这些订单可以在 Document 表中使用条件 (d.DocumentTypeEnum = 'Order' & d.PaymentStatusEnum = 'Paid') 进行标识。财务数据（数量和小计）位于 DocumentLine 中。

如何排除 DocumentLine 表中链接到 DocumentTypeEnum 不是“订单”且 PaymentStatusEnum 不是“已付款”的文档（通过 DocumentId 链接）的订单/数据？

此时 DocumentLine 包括已付订单、失败订单、未付订单、待处理订单、购物车等。因此，数量和小计数据远高于我们实际销售/生成的数据。

注意：我仍然想显示所有 Sku WHERE Barcode IS NOT NULL。因此，如果 DocumentLine 中没有 Sku 的日期，则 Qty 和 SubTotal 值应该是 0 吗？

我在 MySQL 中查询

样本数据

Product
Sku | Barcode
1   | A
2   | B
3   | 
4   | C
5   |
6   | D

DocumentLine
Sku | Qty | SubTotal | DocumentId
1   | 1   | 100      | 123
2   | 1   | 150      | 124
4   | 2   | 400      | 125
6   | 1   | 120      | 128
1   | 2   | 200      | 129
4   | 1   | 200      | 131
3   | 1   | 600      | 127

Document
DocumentId | DocumentTypeEnum | PaymentStatusEnum
123        | Order            | Paid
124        | Cart             | NotApplicable
125        | Order            | Pending
126        | Cart             | NotApplicable
127        | Cart             | NotApplicable
128        | Order            | Failed
129        | Order            | Paid
130        | Cart             | NotApplicable
131        | Order            | Paid

Result:
Sku | Barcode | AmountIncl | Qty | SubTotal
1   | A       | 50         | 3   | 300
2   | B       | 60         | 0   | 0
4   | C       | 40         | 1   | 200
6   | D       | 80         | 0   | 0

标签： mysqlsql

解决方案

    SELECT p.Sku, p.Barcode, s.AmountIncl, 
                CASE WHEN SUM(doc.Qty) IS NULL THEN 0
                ELSE SUM(doc.Qty)
            END AS Qty,
            CASE WHEN SUM(doc.SubTotal) IS NULL THEN 0
                ELSE SUM(doc.SubTotal)
            END AS SubTotal
        FROM Product AS p
           LEFT JOIN (Select dl.sku as sku, dl.qty as qty, dl.subtotal as subtotal 
               from DocumentLine dl, Document d where dl.DocumentId = d.DocumentId
              AND d.DocumentTypeEnum = 'Order' AND d.PaymentStatusEnum = 'Paid') AS doc 
              ON p.Sku = doc.Sku
           LEFT JOIN StandardPrice AS s ON p.ProductId = s.ProductId

        WHERE p.Barcode IS NOT NULL
GROUP BY p.Sku

推荐阅读