mysql的case when 和set 变量的使用

业务要求：

城市：如皋
1.订单金额小于1.5元订单数
2. 订单金额大于等于1.5小于2元的订单数
3.订单金额大于等于2小于2.5元的订单数
4.订单金额大于等于2.5元订单数

sql语句：

set @betinTime='2019-08-08 00:00:00';
set @endTime='2019-08-08 23:59:59';

select  tt.h1,tt.h2,tt.h3,tt.h4, sum(tt.h1+tt.h2+tt.h3+tt.h4)  from (
select   count(case when t.total_amount <1.5 then 1 else null end ) as h1,count(case when t.total_amount >= 1.5 and t.total_amount<2 then 1 else null end ) as h2, count(case when t.total_amount >=2 and t.total_amount<2.5 then 1 else null end ) as h3,count(case when t.total_amount >=2.5 then 1 else null end ) as h4 from (
select a.order_no,a.total_amount  from orders_0 as a,orders_user_0 as b where a.order_no=b.order_no and  b.end_time>= @betinTime and b.end_time<=@endTime  and a.operate_area_id=1065  and a.company_id=42  union all
select a.order_no,a.total_amount  from orders_1 as a,orders_user_1 as b where a.order_no=b.order_no and  b.end_time>= @betinTime and b.end_time<=@endTime  and a.operate_area_id=1065  and a.company_id=42  union all
select a.order_no,a.total_amount  from orders_2 as a,orders_user_2 as b where a.order_no=b.order_no and  b.end_time>= @betinTime and b.end_time<=@endTime  and a.operate_area_id=1065  and a.company_id=42  union all
select a.order_no,a.total_amount  from orders_3 as a,orders_user_3 as b where a.order_no=b.order_no and  b.end_time>= @betinTime and b.end_time<=@endTime  and a.operate_area_id=1065  and a.company_id=42  union all
select a.order_no,a.total_amount  from orders_4 as a,orders_user_4 as b where a.order_no=b.order_no and  b.end_time>= @betinTime and b.end_time<=@endTime  and a.operate_area_id=1065  and a.company_id=42  union all
select a.order_no,a.total_amount  from orders_5 as a,orders_user_5 as b where a.order_no=b.order_no and  b.end_time>= @betinTime and b.end_time<=@endTime  and a.operate_area_id=1065  and a.company_id=42  union all
select a.order_no,a.total_amount  from orders_6 as a,orders_user_6 as b where a.order_no=b.order_no and  b.end_time>= @betinTime and b.end_time<=@endTime  and a.operate_area_id=1065  and a.company_id=42  union all
select a.order_no,a.total_amount  from orders_7 as a,orders_user_7 as b where a.order_no=b.order_no and  b.end_time>= @betinTime and b.end_time<=@endTime  and a.operate_area_id=1065  and a.company_id=42  union all
select a.order_no,a.total_amount  from orders_8 as a,orders_user_8 as b where a.order_no=b.order_no and  b.end_time>= @betinTime and b.end_time<=@endTime  and a.operate_area_id=1065  and a.company_id=42  union all
select a.order_no,a.total_amount  from orders_9 as a,orders_user_9 as b where a.order_no=b.order_no and  b.end_time>= @betinTime and b.end_time<=@endTime  and a.operate_area_id=1065  and a.company_id=42 
)as t
)as tt

结果：