分类汇总按条件

[lsyyfj]

我有这样的一个表，想查询出汇总金额，成本，行数，按年款中把有含有FA做为一类，把RD+第四位的做为一类，RE的做为一个类型进行汇总这样的查询如何写代码



年款 类型 个数 金额 成本 行数
RD55 YZ 3 135 107.6868 1
RD55 KWY 2 136 108.810234 2
RE48 PJ 19 690 542.2716 17
RE47 PJ 1 352 280.8 1
FA18 PJ 8 140 111.1032 8
RD76 PJ 8 580 463.554 8
FA16 PJ 18 1897.69 1517.0337 20
RE48 YZ 6 425 311.851215 3
RD56 PJ 2 35 27.7758 2
RD76 YZ 3 135 107.6868 1
RD76 KWY 4 272 217.620468 4
RD56 KWY 1 108 85.995234 1
FA16 KWY 3 324 257.985702 5
RE48 KWY 10 680 544.05117 10
RD75 PJ 1 320 255.996 1
FA17 PJ 30 3172 2442.3048 28

[csdyyr]

select 'FA' as 年款,sum(金额) as 金额,sum(成本) as 成本,sum(行数) as 行数
from tb 
where charindex('FA',年款)>0 
union all
......

[让你望见影子的墙]

select 'FA' as 年款,sum(金额) as 金额,sum(成本) as 成本,sum(行数) as 行数
from tb
group by left(年款,2)
union all
select 'FA' as 年款,sum(金额) as 金额,sum(成本) as 成本,sum(行数) as 行数
from tb
group by left(年款,2)+substrint(年款,4,1)

[lsyyfj]

select substring(a.fdph,4,3)+substring(a.fdph,10,1) 年款,c.fpjlx,
sum(b.fsl) 数量,sum(b.fje) 销售价,sum(b.fgj*1.17*b.fsl) 含税成本,count(*) 行数
from tb_jswts a
join tb_wgckmx b on a.fjsdjh=b.fjsdjh
join tb_sypj c on c.fpjdm+C.FCKDM=b.fpjdm+B.FCKDM
where ffjsbz='F' and C.FZZCDM='HONDA'AND a.fjsrq>='2008-11-26' and a.fjsrq<='2008-11-27'
group by (substring(a.fdph,4,3)+substring(a.fdph,10,1)),c.fpjlx
这个是查询的代码，能不能再帮助修改一下

[lsyyfj]

LVHRE486185002534 KWY 1.00 108.0000 85.99523400
LVHFA162585041494 KWY 1.00 108.0000 85.99523400
LVHRE486185002534 YZ 1.00 164.0000 114.65941500
LVHFA164165016851 YZ 1.00 123.0000 87.75000000
LVHFA164165016851 YZ 1.00 35.0000 23.61165300
LVHRD777065010815 PJ 1.00 157.0000 125.59950000
LVHRD787165015004 PJ 1.00 157.0000 125.59950000
LVHRD777065010815 PJ 1.00 98.0000 78.40170000
LVHRD787165015004 PJ 1.00 98.0000 78.40170000
LVHRD584055002236 PJ 4.00 2880.0000 2224.03942800
LVHRD777255013858 PJ 1.00 320.0000 255.99600000
LVHRE488285031655 PJ -1.00 -335.0000 -268.00020000
LVHRE488285031655 PJ 1.00 335.0000 268.00020000
LVHFA154265026984 PJ 1.00 441.0000 352.75500000
LVHFA154265026984 PJ 1.00 441.0000 352.75500000
LVHFA154265026984 PJ -1.00 -441.0000 -352.75500000
LVHRD584055002236 PJ -1.00 -16.0000 -12.05100000
LVHRD584055002236 PJ 1.00 16.0000 12.05100000
LVHFA162675072557 PJ 1.00 1134.9000 907.92000000
LVHFA152765039602 PJ 1.00 91.0000 73.00800000
LVHFA152575003327 PJ 1.00 91.0000 73.00800000
LVHFA154265026984 PJ 1.00 91.0000 73.00800000
LVHFA154365020482 PJ 1.00 91.0000 73.00800000
LVHFA152775009954 PJ 1.00 91.0000 73.00800000
LVHFA152275024832 PJ 1.00 690.0000 468.00000000
LVHFA152775009954 PJ 1.00 65.0000 51.48000000
LVHRE486185002534 PJ 4.00 156.0000 122.80320000
LVHRE486185002534 PJ 1.00 2.0000 1.36890000
LVHFA162585041494 PJ 1.00 2.0000 1.36890000
LVHFA152575003327 PJ 1.00 2.0000 1.36890000
LVHRD787165015004 PJ 1.00 2.0000 1.36890000
LVHRE486685021955 PJ 1.00 2.0000 1.36890000
LVHFA152X75024836 YZ 2.00 126.0000 89.50500000
LVHRE486185002534 YZ 2.00 126.0000 89.50500000
LVHRE486185002534 PJ 1.00 81.0000 64.58400000
LVHFA164375045768 PJ 1.00 81.0000 64.58400000
LVHFA152975001399 PJ 1.00 81.0000 64.58400000
LVHFA152X75024836 PJ 1.00 81.0000 64.58400000
LVHFA154365020482 PJ 1.00 11.1300 8.90370000
LVHRD584055002236 PJ 1.00 14.0000 10.57680000
LVHFA152X75024836 YZ 3.00 111.0000 88.45200000
LVHFA152X75024836 PJ 2.00 88.0000 63.99900000
这个是原表

[lsyyfj]

车架号，类型，数量，金额，成本，行数