select case when ordertype =2 then '小计' else bm end as bm,je
from (
select a.bm,b.je,1 as ordertype from 编码表 a left join 金额表 b on a.bmb=b.bmb
union all
select max(bm) as bm,sum(je) as je,2 as ordertype from 金额表
) as c
order by bm,ordertype
from (
select a.bm,b.je,1 as ordertype from 编码表 a left join 金额表 b on a.bmb=b.bmb
union all
select max(bm) as bm,sum(je) as je,2 as ordertype from 金额表
) as c
order by bm,ordertype
from (
select a.bm,b.je,1 as ordertype from 编码表 a left join 金额表 b on a.bmb=b.bmb
union all
select max(bm) as bm,sum(je) as je,2 as ordertype from 金额表 group by left(bm,2)
) as c
order by bm,ordertype
select * from 金额表
union all
select left(bm,2)+'小计' as bm,sum(je) as sum_je from 金额表
group by left(bm,2)+'小计'
order by bm
from 金额表
union all
select left(bm,2)+'小计' as bm,sum(isnull(je,0)) as je
from 金额表
group by left(bm,2)+'小计'
order by bm
1、不必连编码表。
2、只在最后一级才有数据,即在金额表中出现的所有编码都是最后一级的。
3、编码不定长,每级两位,最后一级编码有的是第5级,有的只是第1级,如010101 是最后一级,0102 可能是最后一级,可能0103010101是最后一级,也可能03就是最后一级。
4、要求统计时显示出每个最后一级的所有上级编码。对01010101、01010102
0102、0103010101...、举例如下:bm je
01 //(有下一级,无数据,只要编码)
0101 //(有下一级,无数据,只要编码)
010101 5.00 //(本级中的最后一级,有数据)
010102 6.00 //(有下一级,无数据,只要编码)
0102 // (有下一级,无数据,只要编码)
0102 7.00 //(本级中的最后一级,有数据)
0103 //(有下一级,无数据,只要编码)
010301 //(有下一级,无数据,只要编码)
01030101 //(有下一级,无数据,只要编码)
0103010101 8.00 //(本级中的最后一级,有数据)
0103010102 9.00 //(本级中的最后一级,有数据)
010302 //(有下一级,无数据,只要编码)
01030201 1.00 //(本级中的最后一级,有数据)
01030202 2.00 //(本级中的最后一级,有数据)
...
01合计 xxx.xx //合计只要第一级合计
02
...
02合计 xxx.xx
如要合计增加复杂度,可不要合计。各位大侠,清除了吗?有劳各位了。谢谢
from (
select a.bm,b.je,1 as ordertype from 编码表 a left join 金额表 b on a.bm=b.bm
union all
select max(bm) as bm,sum(je) as je,2 as ordertype from 金额表 group by left(bm,2)
) as c
order by bm,ordertype
在金额表中只存有最后一级的编码,但在报表显示中,要把其上级(每一级)编码都显示出来,不必合计,只按第一级合计就可以了。
麻烦的是,编码不定长,即最后一级编码长度不定,又要显示出其上级编码。
编码表
bm(编码) mc(名称)
01 xxx
0101 xxx
010101(最后一级) xxx
010102(最后一级) xxx
0102 xxx
...
02 xxx
0201 xxx
...
03 xxx
...
金额表
bm je
010101 5.00
010102 6.00
金额表中不会出现编码01,0101,因为01、0101都不是最后一级编码。只能出现0101以下级的编码:010101、010102
但统计时如果有010101和010102的数据,应显示以下格式:01 (空)
0101 (空)
010101 5.00
010102 6.00
01合计 11.00
不知说明白了没有。谢谢,让您费心了。
回复人: rewiah(乘长风) ( ) 信誉:100 2002-04-06 08:55:00 得分:0
select case when ordertype =2 then left(bm,2)+'小计' else bm end as bm,je
from (
select a.bm,b.je,1 as ordertype from 编码表 a left join 金额表 b on a.bm=b.bm
union all
select max(bm) as bm,sum(je) as je,2 as ordertype from 金额表 group by left(bm,2)
) as c
order by bm,ordertype
谢谢,明天上班试一下。
试了,有问题,好多重复,金额表中没有的编码也出现了。能否再写一个不连编码表的。谢谢
01 (空)
0101 (空) 试试以下语句:select case when ordertype =2 then left(bm,2)+'小计' else bm end as bm,je
from (
select a.bm,b.je,1 as ordertype from 编码表 a left join 金额表 b on a.bm=b.bm
where exists (
select 1 from 金额表 d where a.bm like left(d.bm,2)+'%'
)
union all
select max(bm) as bm,sum(je) as je,2 as ordertype from 金额表 group by left(bm,2)
) as c
order by bm,ordertype