将2个查询合并为一个查询

Question

我有两个问题

Select * From wp_booking_transaction
Where DATE(launched) >= "2013-10-10" And DATE(launched) <= "2013-11-10" 
And action = 1 status = 1 And type = 2 And student_id = 81569 
Order by launched desc

和

Select * From wp_booking_transaction 
Where (DATE(launched) >= "2013-10-10" And DATE(launched) <= "2013-11-10" 
And status = 0 And type = 2 And student_id = 81569) And (action = 21 Or action = 20) 
Order by launched desc

所需经费：

获取具有action = 1但有status = 1的行和具有(action = 20 or action = 21)但有status = 0的行。

谢谢!

Answer 1

SELECT   *
FROM     wp_booking_transaction
WHERE    /* Conditions that appeared in both original queries */
         DATE(launched) >= "2013-10-10" 
AND      DATE(launched) <= "2013-11-10"
AND      type = 2 
AND      student_id = 81569
AND      /* Conditions that are different between the two queries */
         ((action = 1 AND status = 1) OR (action IN(20, 21) AND status = 0))
ORDER BY launched DESC

Answer 2

尝试使用UNION

Select 
    * 
From 
    wp_booking_transaction 
Where 
    DATE(launched) >= "2013-10-10" 
    And DATE(launched) <= "2013-11-10" 
    And action = 1 
    And status = 1 
    And type = 2 
    And student_id = 81569 

UNION ALL

Select 
    * 
From 
    wp_booking_transaction 
Where (
        DATE(launched) >= "2013-10-10" 
        And DATE(launched) <= "2013-11-10" 
        And status = 0 
        And type = 2 
        And student_id = 81569
    ) 
    And (
        action = 21 
        Or action = 20
    ) 
Order by launched desc

BTW如下：

And (
        action = 21 
        Or action = 20
    ) 

可以写成

And action IN (21,20)

Answer 3

您可以在两个查询之间使用并-- all _union_ queries 。

Select * From wp_booking_transaction
Where DATE(launched) >= "2013-10-10" And DATE(launched) <= "2013-11-10" 
And action = 1 status = 1 And type = 2 And student_id = 81569 
union all
Select * From wp_booking_transaction 
Where (DATE(launched) >= "2013-10-10" And DATE(launched) <= "2013-11-10" 
And status = 0 And type = 2 And student_id = 81569) And (action = 21 Or action = 20) 
Order by launched desc