求SQL语句或存储过程

[vcforeverya]

A表：
月份 金额
2007-9 67
2007-7 99
2007-7 66

？分别求出A表中小于100 100-200 大于200的记录数，然后插入B表中：

得到的结果应该是下面这样
B表
月份 小于100(条) 100-200(条) 大于200(条)
2007-7 2 0 0
2007-9 1 0 0

[tds__609]

up~~

[giftzheng]

--insert into B表

select 月份 sum(a) as 小于100(条), sum(b) as a100-200(条), sum(c) as 大于200(条)
from
(
select 月份 ,count(*) as a,0 as b,0 as c
from A表
where 金额<100
group by 月份
union all
select 月份 ,0 as a, count(*) as b,0 as c
from A表
where 金额>=100 and 金额<=200
group by 月份
union all
select 月份 ,0 as a, 0 as b, count(*) as c
from A表
where 金额>100
group by 月份
) as temp

group by 月份

[giftzheng]

后面写错了
是
金额>200

[elvis_gao]

select 月份,
小于100=sum(case when 金额<100 then 1 else 0 end),
100-200=sum(case when 金额>=100 AND 金额<=200 then 1 else 0 end),
大于200=sum(case when 金额>200 then 1 else 0 end)
from 表A
group by 月份