select bh,year(rq)+month(rq) as ym,sl where sl>250
select bh ,rq,sum(sl) from table group by rq having sum(sl)>250; 没验证不过应该对了吧
select a.bh,sum(a.sl) from (select bh,convert(t.rq,varchar(6)),sl from tb t) a group by a.bh
测试: create table test2 ( bh varchar(4), rq varchar(8), sl float ) goinsert into test2 select '0001','20000403', 23 union all select '0001','20000403', 280 union all select '0001','20000404', 40 union all select '0001','20000504', 50 union all select '0002','20000408', 203 union all select '0003','20000508', 260 goselect a.bh,a.rq,sum(a.sl) as sl from (select t.bh,convert(varchar(6),t.rq) as rq,sl from test2 t) a group by a.bh,a.rq having sum(a.sl) > 250测试结果: 0001 200004 343 0003 200005 260
bh rq sl 0001 20000403 23 0001 20000403 280 0001 20000404 40 0001 20000504 50 0002 20000408 203 0003 20000508 260select bh,substring(rq,1,6),sum(sl) as zsl from table (表名) group by bh,substring(rq,1,6) having sum(sl) >250 结果 0001 200004 343 0003 200005 260
from table
group by rq
having sum(sl)>250;
没验证不过应该对了吧
from (select bh,convert(t.rq,varchar(6)),sl
from tb t) a
group by a.bh
create table test2
(
bh varchar(4),
rq varchar(8),
sl float
)
goinsert into test2
select '0001','20000403', 23 union all
select '0001','20000403', 280 union all
select '0001','20000404', 40 union all
select '0001','20000504', 50 union all
select '0002','20000408', 203 union all
select '0003','20000508', 260
goselect a.bh,a.rq,sum(a.sl) as sl
from
(select t.bh,convert(varchar(6),t.rq) as rq,sl
from test2 t) a
group by a.bh,a.rq
having sum(a.sl) > 250测试结果:
0001 200004 343
0003 200005 260
0001 20000403 23
0001 20000403 280
0001 20000404 40
0001 20000504 50
0002 20000408 203
0003 20000508 260select bh,substring(rq,1,6),sum(sl) as zsl from table (表名)
group by bh,substring(rq,1,6)
having sum(sl) >250
结果
0001 200004 343
0003 200005 260