mysql - 当两年都存在发票时，按年份获取总计

Question

因此，我正在查找两年内都有发票的每个客户的发票总数。所以我有

SELECT     Sum((tbl_invoice_details.quantity 
                - ifnull(tbl_invoice_details.quantity_returned, 0)
                ) * tbl_invoice_details.price) AS dollarsSold,
           tbl_customers.NAME,
           cust_ID AS customer_id,
           YEAR (tbl_invoice.invoice_date) AS invoiceYear
FROM       `tbl_invoice_details`
INNER JOIN `tbl_invoice` 
ON         `tbl_invoice_details`.`invoice_ID` = `tbl_invoice`.`invoice_ID`
INNER JOIN `tbl_customers` 
ON         `tbl_customers`.`customer_ID` = `tbl_invoice`.`cust_ID`
WHERE      MONTH (`invoice_date`) = '2'
AND        (YEAR (`invoice_date`) = '2017'
            OR
            YEAR (`invoice_date`) = '2016')
AND EXISTS (SELECT *
            FROM   tbl_invoice
            WHERE  cust_ID = customer_id
            AND    MONTH (`invoice_date`) = '2'
            AND    YEAR (`invoice_date`) = '2016')
GROUP BY    MONTH (tbl_invoice.invoice_date),
            invoiceYear,
            `tbl_customers`.`name`,
            customer_id
ORDER BY name

那么，我如何将其限制为在 2016 年 2 月和 2017 年 2 月都有发票的客户。我尝试使用 EXISTS 但只成功了一半。

Answer 1

确保两张发票都存在的最佳方法是使用 EXISTS 两次，一次用于 2016 年，另一次用于 2017 年。

SELECT     Sum((tbl_invoice_details.quantity 
                - ifnull(tbl_invoice_details.quantity_returned, 0)
                ) * tbl_invoice_details.price) AS dollarsSold,
           tbl_customers.NAME,
           cust_ID AS customer_id,
           YEAR (tbl_invoice.invoice_date) AS invoiceYear
FROM       `tbl_invoice_details`
INNER JOIN `tbl_invoice` 
ON         `tbl_invoice_details`.`invoice_ID` = `tbl_invoice`.`invoice_ID`
INNER JOIN `tbl_customers` 
ON         `tbl_customers`.`customer_ID` = `tbl_invoice`.`cust_ID`
WHERE      MONTH (`invoice_date`) = '2'
AND        YEAR (`invoice_date`) IN ('2016', '2017')
AND EXISTS (SELECT 1
            FROM   tbl_invoice
            WHERE  cust_ID = customer_id
            AND    MONTH (`invoice_date`) = '2'
            AND    YEAR (`invoice_date`) = '2016')
AND EXISTS (SELECT 1
            FROM   tbl_invoice
            WHERE  cust_ID = customer_id
            AND    MONTH (`invoice_date`) = '2'
            AND    YEAR (`invoice_date`) = '2017')
GROUP BY    MONTH (tbl_invoice.invoice_date),
            invoiceYear,
            `tbl_customers`.`name`,
            customer_id
ORDER BY name;