请看下面的程序
select sm_dept,deptname,substring(sa_product,1,4),cl_name,
case when substring(sa_product,1,4) in(1101,1102,1103) then sum(saleamt) else 0 end num1,
case when substring(sa_product,1,4) between 1104 and 2000 then sum(saleamt) else 0 end num2
from td_sale_m,td_sale_s a,td_dept,td_product,td_class
where sm_sheetno=sa_sheetno and productno=sa_product
and sm_dept=deptno and substring(sa_product,1,4)=convert(char(4),cl_class)
group by sm_dept,deptname,substring(sa_product,1,4),cl_name
我要把满足substring(sa_product,1,4) between 1104 and 2000 条件的num2
按照sm_dept分类求和，既把在1104到2000的数据全部加起来，最后的结果是这样的四组：
dept 类别 数量
a1 1101 数据
a1 1102 数据
a1 1103 数据
a1 1104－2000 数据和
a2 1101 数据
a2 1102 数据
a2 1103 数据
a2 1104－2000 数据和
a3 1101 数据
a3 1102 数据
a3 1103 数据
a3 1104－2000 数据和
....
程序应该怎么修改
---------------------------------------------------------------
select dept,'1104-2000' as 类别，Sum(数量) as 数量
into #tempTable
from table
where substring(sa_product,1,4) between 1104 and 2000 (条件是你写的，没仔细看)
group by dept
insert into #temptabel(dept,类别，数量)
select dept,'1104-2000' as 类别，Sum(数量)
from table
where not (substring(sa_product,1,4) between 1104 and 2000 )
select * from #temptable order by dept,类别
---------------------------------------------------------------
select sm_dept,deptname,
case when 类别='1101' then sum(num) else 0 end,
case when 类别='1102' then sum(num) else 0 end,
case when 类别='1103' then sum(num) else 0 end,
case when 类别='other' then sum(num) else 0 end
from #temptable
group by sm_dept,deptname,类别
---------------------------------------------------------------
case when (substring(sa_product,1,4) in(1101,1102,1103)) then substring(sa_product,1,4)
when (substring(sa_product,1,4) between 1104 and 2000) then '1104-2000'
else 'others' end as 类别,
sum(saleamt) as 数量
-----
group by by sm_dept,类别