mysql - 如何将两个带有 having 子句的查询合并为一个？

Question

我希望你能帮我解决这两个问题查询:

select count(distinct ID), sum(AMOUNT) 
from SALES 
where YEAR(SALEDATE) = '2015' and SALETYPE ='CASH'
group by CUSTOMER having sum(AMOUNT) >=500;

select count(distinct ID), sum(AMOUNT) 
from SALES 
where YEAR(SALEDATE) = '2015' and SALETYPE ='CREDITCARD'
group by CUSTOMER having sum(AMOUNT) >=1000;

将它们合二为一的最佳方式是什么？我的目标是选择客户以现金支付超过 500 美元或通过信用卡支付超过 1000 美元的所有情况。

非常感谢您的帮助。

Answer 1

在你的情况下，你可以使用这个例子:

select  count(distinct ID), 
        sum(AMOUNT) 
from    SALES 
where   YEAR(SALEDATE) = '2015' 
    and SALETYPE ='CASH' 
group by CUSTOMER 
having  sum(AMOUNT) >=500;

UNION

select  count(distinct ID), 
        sum(AMOUNT) 
from    SALES 
where   YEAR(SALEDATE) = '2015' 
    and SALETYPE ='CREDITCARD' 
group by CUSTOMER 
having  sum(AMOUNT) >=1000;

OR UNION ALL 如果你想要所有没有distinct的记录。

select  count(distinct ID), 
        sum(AMOUNT) 
from    SALES 
where   YEAR(SALEDATE) = '2015' 
    and SALETYPE ='CASH' 
group by CUSTOMER 
having  sum(AMOUNT) >=500;

UNION ALL

select  count(distinct ID), 
        sum(AMOUNT) 
from    SALES 
where   YEAR(SALEDATE) = '2015' 
        and SALETYPE ='CREDITCARD' 
group by CUSTOMER 
having  sum(AMOUNT) >=1000;