sql-统计某个区间
合同表contract,查询某年合同金额区间数量(区间内没值时数量为0)
select sum(case when scope='15万以下' then 1 else 0 end) as 15万以下, sum(case when scope='15至100万' then 1 else 0 end) as 15至100万, sum(case when scope='100至500万' then 1 else 0 end) as 100至500万, sum(case when scope='500万以上' then 1 else 0 end) as 500万以上 from ( SELECT con.contractMoney, CASE WHEN contractMoney < 15 THEN '15万以下' WHEN contractMoney BETWEEN 15 AND 100 THEN '15至100万' WHEN contractMoney BETWEEN 100 AND 500 THEN '100至500万' ELSE '500万以上' END AS scope FROM t_contract con WHERE con.isAvailable =1 AND YEAR (con.contractDate) =2017 ) t1
显示结果为: