MySQL - 如何执行一次子查询来计算许多其他字段？

Question

下一个查询示例计算totalSubjectToTax和totalExemptToTax。为此，查询正在执行子查询，但在两个计算中重复它。如何只执行一次子查询就获得totalSubjectToTax和totalExemptToTax？

SELECT
    documentId,
    (
        SUM(IF(
            di.document_item_subjectToTax = true,
            di.document_item_amount,
            0
        ))
        * (1 - IFNULL((SELECT ... subquery code ...), 0))
    ) AS totalSubjectToTax,
    (
        SUM(IF(
            di.document_item_subjectToTax = false,
            di.document_item_amount,
            0
        ))
        * (1 - IFNULL((SELECT ... subquery code ... ), 0))
    ) AS totalExemptToTax
FROM document
LEFT JOIN document_item AS di ON di.document_documentId = documentId
GROUP BY documentId

子查询代码:

SELECT
    SUM(document_discount_percentage / 100)
FROM document_discount
GROUP BY document_documentId

数据样本:

documentId
         1
         2
         3
         4
         5
         6

document_documentId | document_item_subjectToTax | document_item_amount
                  1                         true                   1000
                  1                         true                   1500
                  2                         true                    500
                  3                        false                    600
                  3                         true                    900
                  4                        false                   2700
                  5                         true                    400
                  5                         true                   7000
                  6                        false                   2400
                  6                        false                   1100

document_documentId | document_discount_percentage
                  1                              5
                  3                             10
                  3                              5
                  6                              4

预期结果:

documentId | totalSubjectToTax | totalExemptToTax
         1                2375                  0
         2                 500                  0
         3                 510                765
         4                   0               2700
         5                7400                  0
         6                   0               3360

谢谢

Answer 1

与子查询连接:

SELECT
    d.documentId,
    SUM(IF(
        di.document_item_subectToTax, 
        di.document_item_price * di.document_item_quantity, 
        0
    )) * IFNULL((1 - dd.totalDiscountPercentage), 1) AS totalSubjectToTax,
    SUM(IF(
        !di.document_item_subectToTax, 
        di.document_item_price * di.document_item_quantity, 
        0
    )) * IFNULL((1 - dd.totalDiscountPercentage), 1) AS totalNotSubjectToTax
FROM document AS d
LEFT JOIN (
    SELECT
        document_documentId,
        SUM(document_discount_percentage / 100) AS totalDiscountPercentage 
    FROM document_discount
    GROUP BY document_documentId
) AS dd ON d.documentId = dd.document_documentId
LEFT JOIN document_item AS di.document_documentId = d.documentId
GROUP BY d.documentId