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"