mysql查询中UNION的WHERE子句组合

Question

在使用UNION从两个表中获取数据时，我有一个查询，它正确显示，但当我必须选择不同的日期间隔以显示这些日期之间的数据时，问题就出现了，它只需要一个where子句，并将其应用于单个select部分，而不是整个查询。我需要把它应用于整个.多么?

SELECT
    TICKETS.TICKETID AS TICKET_NO,
    RECEIPTS.DATENEW AS DATENEW,
    PAYMENTS.TOTAL AS MONEY,
    CUSTOMERS.NAME AS CUSTOMER,
    PAYMENTS.PAYMENT AS PAYMENT
FROM RECEIPTS
    LEFT JOIN TICKETS ON RECEIPTS.ID = TICKETS.ID
    LEFT JOIN PAYMENTS ON RECEIPTS.ID = PAYMENTS.RECEIPT
    LEFT JOIN CUSTOMERS ON TICKETS.CUSTOMER = CUSTOMERS.ID
WHERE ?(QBF_FILTER)
UNION SELECT
    ADJUSTMENTS.TICKET_NO AS TICKET_NO,
    ADJUSTMENTS.DATE AS DATENEW,
    ADJUSTMENTS.ADJUSTMENT_AMOUNT AS MONEY,
    ADJUSTMENTS.CUSTOMER_NAME AS CUSTOMER,
    ADJUSTMENTS.ADJUSTMENT_TYPE AS PAYMENT
FROM ADJUSTMENTS
    LEFT JOIN CUSTOMERS ON CUSTOMERS.ID = ADJUSTMENTS.CUSTOMER_ID
    WHERE ?(QBF_FILTER)

上面的查询来自UNICENTA开放源代码项目，该项目使用文件生成报表，当我将WHERE子句放在两个select部分上时，它会生成SQL语法错误，现在我讨论的问题就是我需要解决的问题，谢谢：

Answer 1

您可以通过嵌套的选择来完成这一任务：

SELECT * FROM (
    SELECT
        TICKETS.TICKETID AS TICKET_NO,
        RECEIPTS.DATENEW AS DATENEW,
        PAYMENTS.TOTAL AS MONEY,
        CUSTOMERS.NAME AS CUSTOMER,
        PAYMENTS.PAYMENT AS PAYMENT
    FROM RECEIPTS
        LEFT JOIN TICKETS ON RECEIPTS.ID = TICKETS.ID
        LEFT JOIN PAYMENTS ON RECEIPTS.ID = PAYMENTS.RECEIPT
        LEFT JOIN CUSTOMERS ON TICKETS.CUSTOMER = CUSTOMERS.ID
    WHERE ?(QBF_FILTER)
    UNION SELECT
        ADJUSTMENTS.TICKET_NO AS TICKET_NO,
        ADJUSTMENTS.DATE AS DATENEW,
        ADJUSTMENTS.ADJUSTMENT_AMOUNT AS MONEY,
        ADJUSTMENTS.CUSTOMER_NAME AS CUSTOMER,
        ADJUSTMENTS.ADJUSTMENT_TYPE AS PAYMENT
    FROM ADJUSTMENTS
        LEFT JOIN CUSTOMERS ON CUSTOMERS.ID = ADJUSTMENTS.CUSTOMER_ID
        WHERE ?(QBF_FILTER)
    )
WHERE (yourDate between Date1 and Date2)

编辑：--我想它需要更像：

SELECT * FROM (
    SELECT
        TICKETS.TICKETID AS TICKET_NO_REC,
        RECEIPTS.DATENEW AS DATENEW_REC,
        PAYMENTS.TOTAL AS MONEY_REC,
        CUSTOMERS.NAME AS CUSTOMER_REC,
        PAYMENTS.PAYMENT AS PAYMENT_REC
    FROM RECEIPTS
        LEFT JOIN TICKETS ON RECEIPTS.ID = TICKETS.ID
        LEFT JOIN PAYMENTS ON RECEIPTS.ID = PAYMENTS.RECEIPT
        LEFT JOIN CUSTOMERS ON TICKETS.CUSTOMER = CUSTOMERS.ID
    UNION SELECT
        ADJUSTMENTS.TICKET_NO AS TICKET_NO_ADJ,
        ADJUSTMENTS.DATE AS DATENEW_ADJ,
        ADJUSTMENTS.ADJUSTMENT_AMOUNT AS MONEY_ADJ,
        ADJUSTMENTS.CUSTOMER_NAME AS CUSTOMER_ADJ,
        ADJUSTMENTS.ADJUSTMENT_TYPE AS PAYMENT_ADJ
    FROM ADJUSTMENTS
        LEFT JOIN CUSTOMERS ON CUSTOMERS.ID = ADJUSTMENTS.CUSTOMER_ID
    )
WHERE ?(QBF_FILTER)