SET @qry=@qry+char(13)+ ' SELECT ' SET @qry=@qry+char(13)+ ' case when GROUPING_ID(stockInDay,materialCode)=''1'' then ''소계'' when GROUPING_ID(stockInDay,materialCode)=''3'' then ''총계'' else stockInDay end as stockInDay,' SET @qry=@qry+char(13)+ ' materialCode, ' --SET @qry=@qry+char(13)+ ' materialName, ' SET @qry=@qry+char(13)+ ' case when materialName is null and materialCode is not null then ''소계'' else materialName end materialName , ' SET @qry=@qry+char(13)+ ' dimension, ' SET @qry=@qry+char(13)+ ' domesticText, ' SET @qry=@qry+char(13)+ ' sum(realQty) realQty, ' SET @qry=@qry+char(13)+ ' unitName, ' SET @qry=@qry+char(13)+ ' sellerCode, ' SET @qry=@qry+char(13)+ ' sellerName, ' SET @qry=@qry+char(13)+ ' storeCode, ' SET @qry=@qry+char(13)+ ' storeName, ' SET @qry=@qry+char(13)+ ' isSellingvat, ' SET @qry=@qry+char(13)+ ' sellingPrice, ' SET @qry=@qry+char(13)+ ' sum(sellingTotal) sellingTotal, ' SET @qry=@qry+char(13)+ ' sum(sellingAmt) sellingAmt, ' SET @qry=@qry+char(13)+ ' sum(sellingVat) sellingVat, ' SET @qry=@qry+char(13)+ ' remark, ' SET @qry=@qry+char(13)+ ' restrictCount, ' SET @qry=@qry+char(13)+ ' modifierDate, ' SET @qry=@qry+char(13)+ ' sellermaterialcode, ' SET @qry=@qry+char(13)+ ' remark2, ' SET @qry=@qry+char(13)+ ' handphone, ' SET @qry=@qry+char(13)+ ' address ' SET @qry=@qry+char(13)+ ' FROM (' SET @qry=@qry+char(13)+ ' select ' SET @qry=@qry+char(13)+ ' a.stockInDay, ' SET @qry=@qry+char(13)+ ' a.materialCode, ' SET @qry=@qry+char(13)+ ' b.materialName, ' SET @qry=@qry+char(13)+ ' b.dimension, ' SET @qry=@qry+char(13)+ ' b.domesticText, ' SET @qry=@qry+char(13)+ ' a.materialQty-a.rtnQty as realQty, ' SET @qry=@qry+char(13)+ ' DBO.UF_UNITNAME(b.unitCode) as unitName, ' SET @qry=@qry+char(13)+ ' a.sellerCode, ' SET @qry=@qry+char(13)+ ' DBO.UF_SELLERNAME(a.dealerCode,a.sellercode) as sellerName, ' SET @qry=@qry+char(13)+ ' a.storeCode, ' SET @qry=@qry+char(13)+ ' case when a.storecode = ''9999'' then ''창고입고'' ' SET @qry=@qry+char(13)+ ' else ( case when len(replace(a.storetype,'' '',''''))>0 then DBO.UF_STORENAME(a.dealerCode,a.storeCode)+''(''+replace(a.storetype,'' '','''')+'')'' else DBO.UF_STORENAME(a.dealerCode,a.storeCode) end ) end as storeName, ' SET @qry=@qry+char(13)+ ' a.isSellingvat, ' SET @qry=@qry+char(13)+ ' a.sellingPrice, ' SET @qry=@qry+char(13)+ ' a.sellingAmt+a.sellingVat as sellingTotal, ' SET @qry=@qry+char(13)+ ' a.sellingAmt, ' SET @qry=@qry+char(13)+ ' a.sellingVat, ' SET @qry=@qry+char(13)+ ' a.remark, ' SET @qry=@qry+char(13)+ ' (case when a.orders_restrictcount is null then ''D-''+isnull(b.restrictCount,''1'') else ''D-''+a.orders_restrictcount end) as restrictCount, ' SET @qry=@qry+char(13)+ ' a.modifierDate ,' SET @qry=@qry+char(13)+ ' b.sellermaterialcode, ' SET @qry=@qry+char(13)+ ' isnull(a.domesticText,'''') as remark2, ' SET @qry=@qry+char(13)+ ' (select handphone from client where dealerCode='''+@A_dealerCode+''' and clientCode=a.storeCode) as handphone, ' SET @qry=@qry+char(13)+ ' (select address from client where dealerCode='''+@A_dealerCode+''' and clientCode=a.storeCode) as address ' SET @qry=@qry+char(13)+ ' from orders a,material b ' SET @qry=@qry+char(13)+ ' where a.dealerCode='''+@A_dealerCode+''' ' SET @qry=@qry+char(13)+ ' and a.dealerCode=b.dealerCode ' SET @qry=@qry+char(13)+ ' and a.materialCode=b.materialCode ' SET @qry=@qry+char(13)+ ' and a.stockinDay between '''+@A_startDay+''' and '''+@A_endDay+''' ' SET @qry=@qry+char(13)+ ') t ' SET @qry=@qry+char(13)+ 'group by rollup(stockInDay,materialCode,materialName,dimension,domesticText,realQty,unitName,sellerCode,sellerName,storeCode,storeName,isSellingvat,sellingPrice,sellingTotal,sellingAmt,sellingVat,remark,restrictCount,modifierDate,sellermaterialcode,remark2,handphone,address)) ' set @qry=@qry+char(13)+ ' order by case when stockinday is null then 1 else 0 end asc , stockinday asc, case when sellercode is null then 1 else 0 end asc , sellercode asc, case when materialName is null then 1 else 0 end asc , materialName asc, case when materialCode is null then 1 else 0 end asc , materialCode asc'
order by를 걸면 소계총계가 다 밑으로내려가더라구요
방법이있을까요 ㅠㅠ
-- 변경전 -- ORDER BY CASE WHEN stockinday IS NULL THEN 1 ELSE 0 END ASC, stockinday ASC , CASE WHEN sellercode IS NULL THEN 1 ELSE 0 END ASC, sellercode ASC , CASE WHEN materialName IS NULL THEN 1 ELSE 0 END ASC, materialName ASC , CASE WHEN materialCode IS NULL THEN 1 ELSE 0 END ASC, materialCode ASC -- 변경후 -- ORDER BY t.stockinday , t.sellercode , t.materialName , t.materialCode -- 중요 포인트 -- -- 컬럼명을 가공하여 알리아스를 줄 때 컬럼명과 동일하게 알리아스를 주는 경우에는 -- ORDER BY 절 사용에 주의를 기울여야 합니다. -- ORDER BY stockinday 는 가공된 결과에 대한 알리아스를 의미합니다. -- ORDER BY t.stockinday 를 하면 원본 컬럼에 대한 정렬이 됩니다.
-- 롤업 자체를 잘못 사용한 듯 합니다. -- 변경전 : 개별 항목 롤업 -- GROUP BY ROLLUP( stockInDay, materialCode, materialName, dimension, domesticText , realQty, unitName, sellerCode, sellerName, storeCode, storeName , isSellingvat, sellingPrice, sellingTotal, sellingAmt, sellingVat , remark, restrictCount, modifierDate, sellermaterialcode, remark2 , handphone, address ) ; -- 변경후 : 종속항목은 괄호로 묶고, 집계항목은 제거 GROUP BY ROLLUP( stockInDay , ( sellerCode, sellerName , storeCode, storeName , isSellingvat, sellingPrice , remark, modifierDate, remark2, handphone, address ) -- 종속항목은 괄호로 묶고 , ( materialName, materialCode, dimension, domesticText , unitName , restrictCount , sellermaterialcode ) -- 종속항목은 괄호로 묶고 -- , realQty, sellingTotal, sellingAmt, sellingVat -- 집계항목은 제거 ) ; -- 우선 나름 정리를 해보긴 했는데...맞는지는 모르겠네요. -- 항목들간의 종속관계가 파악되어야 합니다. (예, 이름은 코드에 종속) -- 애초에 항목이 너무 많습니다. 불필요한 항목은 없는지? 확인 및 제거가 필요해 보입니다.
rollup(stockInDay,materialCode,(materialName,dimension,domesticText,realQty,unitName, sellerCode,sellerName,storeCode,storeName,isSellingvat,sellingPrice,sellingTotal, sellingAmt,sellingVat,remark,restrictCount,modifierDate,sellermaterialcode,remark2,handphone,address))
아 롤업이 조금 잘못올라갔는데 stockinDay랑 materialCode를 제외한것들은 묶어놧습니다
-- 1. 합계항목 제거 : realQty, sellingTotal, sellingAmt, sellingVat -- 2. 정렬순서에 맞는 롤업 순서 GROUP BY ROLLUP ( stockInDay , ( sellerCode, sellerName , materialName, materialCode ) , ( dimension, domesticText -- , realQty , unitName, storeCode, storeName , isSellingvat, sellingPrice -- , sellingTotal, sellingAmt, sellingVat , remark, restrictCount, modifierDate, sellermaterialcode , remark2, handphone, address ) )
위의사진이 원본입니다.
그리고 댓글에있는게 알려주신대로 rollup만 바꿨습니다.
20200406 1515016 감자 4.00
20200406 1515016 감자 34.00
20200406 1515016 감자 2666.00
20200406 1515016 감자 4.00
20200406 1515017 감자 . 1.00
20200406 1515195 꼬막 . 1.00
20200406 1515139 닭정육 . 1.50
20200406 1515282 맛김 . 0.50
20200406 1515210 바다장어 . 1.00
20200406 1515210 바다장어 . 0.40
20200406 1515053 쑥 . 0.30
20200406 1515134 오이지 . 1.00
20200406 1515134 오이지 . 0.20
20200406 1515024 흙당근 . 1.00
20200406 1515024 흙당근 . 0.80
소계 NULL NULL NULL NULL 8632.70
총계 NULL NULL NULL NULL 8632.70
set @qry= '' SET @qry=@qry+char(13)+ ' select ' SET @qry=@qry+char(13)+ ' a.stockInDay, ' SET @qry=@qry+char(13)+ ' a.materialCode, ' SET @qry=@qry+char(13)+ ' b.materialName, ' SET @qry=@qry+char(13)+ ' b.dimension, ' SET @qry=@qry+char(13)+ ' b.domesticText, ' SET @qry=@qry+char(13)+ ' a.materialQty-a.rtnQty as realQty, ' SET @qry=@qry+char(13)+ ' DBO.UF_UNITNAME(b.unitCode) as unitName, ' SET @qry=@qry+char(13)+ ' a.sellerCode, ' SET @qry=@qry+char(13)+ ' DBO.UF_SELLERNAME(a.dealerCode,a.sellercode) as sellerName, ' SET @qry=@qry+char(13)+ ' a.storeCode, ' SET @qry=@qry+char(13)+ ' case when a.storecode = ''9999'' then ''창고입고'' ' SET @qry=@qry+char(13)+ ' else ( case when len(replace(a.storetype,'' '',''''))>0 then DBO.UF_STORENAME(a.dealerCode,a.storeCode)+''(''+replace(a.storetype,'' '','''')+'')'' else DBO.UF_STORENAME(a.dealerCode,a.storeCode) end ) end as storeName, ' SET @qry=@qry+char(13)+ ' a.isSellingvat, ' SET @qry=@qry+char(13)+ ' a.sellingPrice, ' SET @qry=@qry+char(13)+ ' a.sellingAmt+a.sellingVat as sellingTotal, ' SET @qry=@qry+char(13)+ ' a.sellingAmt, ' SET @qry=@qry+char(13)+ ' a.sellingVat, ' SET @qry=@qry+char(13)+ ' a.remark, ' SET @qry=@qry+char(13)+ ' (case when a.orders_restrictcount is null then ''D-''+isnull(b.restrictCount,''1'') else ''D-''+a.orders_restrictcount end) as restrictCount, ' SET @qry=@qry+char(13)+ ' a.modifierDate ,' SET @qry=@qry+char(13)+ ' b.sellermaterialcode, ' SET @qry=@qry+char(13)+ ' isnull(a.domesticText,'''') as remark2, ' SET @qry=@qry+char(13)+ ' (select handphone from client where dealerCode='''+@A_dealerCode+''' and clientCode=a.storeCode) as handphone, ' SET @qry=@qry+char(13)+ ' (select address from client where dealerCode='''+@A_dealerCode+''' and clientCode=a.storeCode) as address ' SET @qry=@qry+char(13)+ ' from orders a,material b ' SET @qry=@qry+char(13)+ ' where a.dealerCode='''+@A_dealerCode+''' ' SET @qry=@qry+char(13)+ ' and a.dealerCode=b.dealerCode ' SET @qry=@qry+char(13)+ ' and a.materialCode=b.materialCode ' SET @qry=@qry+char(13)+ ' and a.stockinDay between '''+@A_startDay+''' and '''+@A_endDay+''' ' set @qry = @qry + char(13) + ' order by a.stockinday,a.sellercode,b.materialName,b.materialCode '
이게원본코드입니다..
저는 누가수정해놓은걸 바꾸려고했는데
원본에서 하는게 괜찮을까요?
group by rollup((stockInDay),(materialName,materialCode,sellerCode),( dimension, domesticText, realQty, unitName, sellerName, storeCode, storeName, isSellingvat, sellingPrice, sellingTotal, sellingAmt, sellingVat, remark, restrictCount, modifierDate, sellermaterialcode, remark2, handphone, address)) '
이렇게 했습니다 감사합니다