sql - 将 SQL 查询合并为一个

Question

我有这些 SQL 查询:

select count(*) as count1, sum(amount) as amount1 from v_purchase where amount >= 100 and 
amount <  100000 and p_date = '2014-06-12'

select count(*) as count1, sum(amount) as amount1 from v_purchase where amount >= 100000 and amount <  250000 and p_date = '2014-06-12'

select count(*) as count1, sum(amount) as amount1 from v_purchase where amount >= 250000 and amount <  500000 and p_date = '2014-06-12'

select count(*) as count1, sum(amount) as amount1 from v_purchase where amount >= 500000 and amount <  1000000 and p_date = '2014-06-12'

select count(*) as count1, sum(amount) as amount1 from v_purchase where amount >= 1000000 and amount <  2500000 and p_date = '2014-06-12'

select count(*) as count1, sum(amount) as amount1 from v_purchase where amount >= 2500000 and amount <  5000000 and p_date = '2014-06-12'

select count(*) as count1, sum(amount) as amount1 from v_purchase where amount >= 5000000 and amount <  10000000 and p_date = '2014-06-12'

select count(*) as count1, sum(amount) as amount1 from v_purchase where amount >= 10000000  p_date = '2014-06-12'

有没有办法将这些查询组合成一个并将其作为单个查询执行？然后可以在代码中分隔结果。

Answer 1

已更新根据对一系列日期的讨论进行更新。我创建了一个 SQL Fiddle for you

    select  
        SUM(Case When amount >= 100 and amount <  100000 Then 1 else 0 End) as band1Count,
        SUM(Case When amount >= 100000 and amount <  250000 Then 1 else 0 End) as band2Count,
        SUM(Case When amount >= 250000 and amount <  500000 Then 1 else 0 End) as band3Count,
        SUM(Case When amount >= 500000 and amount <  1000000 Then 1 else 0 End) as band4Count,
        SUM(Case When amount >= 1000000 and amount <  2500000 Then 1 else 0 End) as band5Count,
       ...

        SUM(Case When amount >= 100 and amount <  100000 Then amount else 0 End) as band1Sum,
        SUM(Case When amount >= 100000 and amount <  250000 Then amount else 0 End) as band2Sum,
        SUM(Case When amount >= 250000 and amount <  500000 Then amount else 0 End) as band3Sum,
        SUM(Case When amount >= 500000 and amount <  1000000 Then amount else 0 End) as band4Sum,
        SUM(Case When amount >= 1000000 and amount <  2500000 Then amount else 0 End) as band5Sum,
       ...

    from v_purchase 
    where p_date between '2014-06-10' and '2014-06-12'

---