sql case when, Exist ,group by ,聚合

             
                      select  cm.heatno,cm.lotheatno,cm.heatorder
                     ,max(cm.cutdate)cutdate,cm.cutdimensiona,cm.cutdimensionb,cm.length
                     ,sum( cm.weight/1000)weight
                     ,sum( cm.weightwgt/1000)weightwgt,count(cm.qty)qty
                        ,(case status 
                        when 20 then '20-產出等待'
                        when 22 then '22-質量封鎖'
                        when 23 then '23-可編計劃'
                        when 24 then '24-編入計劃'
                        when 29 then '29-待掛訂單'
                        else status 
                        end ) status
                     ,cm.qualitycode,cm.gkno,cm.spec,cm.warehouseno,cm.areano,cm.rowno,cm.ccno as ccnoid,ws.workshopname as ccno,cm.stdprodcode 
                     ,max(convert(nvarchar(50), cm.lastintime, 20))lastintime,cm.slabsrc,cm.memo,bm.vehicleno,bm.providername
                    ,( case cm.msdp 
                        when 00 then '未修磨'
                        when 01 then '全修'
                        when 02 then '角修'
                        when 05 then '全修探傷'
                        when 06 then '角修探傷'
                        when 07 then '拋丸探傷'
                        when 08 then '全修探傷拋丸'
                        when 09 then '角修探傷拋丸'
                        when 11 then '拋丸'
                        when 12 then '探傷'
                        else '' 
                        end    )    msdp     
                    ,( case when cm.msdp is null then ''
                        else '是' 
                        end    )   ismsdp
                     from t_gpkcmx cm 
                    left join t_ibmes25 bm on cm.heatno = bm.heatno 
                    left join b_workshop ws on cm.ccno = ws.workshopcode
                    where cm.status not in (21,54) and exists (select distinct warehouseno from zgzx_basekq where cm.warehouseno = warehouseno) 


    group by cm.heatno,cm.lotheatno,cm.heatorder,cm.cutdimensiona,cm.cutdimensionb,cm.length,cm.qualitycode,cm.gkno,cm.spec,cm.warehouseno,
cm.areano,cm.rowno,cm.ccno,ws.workshopname,cm.stdprodcode ,cm.status ,cm.slabsrc,cm.memo,bm.vehicleno,bm.providername,cm.msdp order by lastintime desc"