데이터베이스 질문과 답변
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 를 하면 원본 컬럼에 대한 정렬이 됩니다.
-- 오라클의 경우 NULL 이 밑으로 가서 위과 같이 해도 되지만
-- MSSQL 의 경우에는 NULL 이 위로 가서 다르게 해야 겠네요.
-- MSSQL --
ORDER BY GROUPING(t.stockinday ), t.stockinday
, GROUPING(t.sellercode ), t.sellercode
, GROUPING(t.materialName), t.materialName
, GROUPING(t.materialCode), t.materialCode
답변감사합니다
품목별소계가 맨밑에 모여서 출력이 되네요 ㅠ
GROUP BY 롤업 항목은?엄청 많은데??정렬 항목은 몇개 안되네요?
뭔가 이상합니다. 정렬기준이 모호하네요.
롤업 항목 순서와 정렬항목 순서가 동일해야 할 것 같은데요.
롤업 항목들을 적절하게 괄호로 묶어줄 필요가 있습니다.
Stockinday > sellercode > materialname > materialcode 순으로 정렬하고싶은데 말씀하신건 rollup에있는 항목들을 다 orderby에 넣으라는말씀이신건가요??
-- 롤업 자체를 잘못 사용한 듯 합니다.
-- 변경전 : 개별 항목 롤업 --
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
)
)
답변감사합니다!! 그런데 품목별소계가 사라집니다..ㅠ
정렬 순서가 materialCode 보다 sellerCode 가 우선 인게 맞는지 의문? (롤업순서와 다름)
원본 대비 결과를 표로 보여주세요. (주요?항목들 포함된 것으로)
위의사진이 원본입니다.
그리고 댓글에있는게 알려주신대로 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
1. 원본일리가 없습니다. 롤업의 결과이겠죠.
2. 항목이 상당히 많은데 보여주신 항목은 몇개 안되네요. (sellerCode 정보도 보여주세요)
3. 롤업 순서가 바뀌면 Select 절도 바뀌어야 합니다. (소계 표시 조건)
4. 정렬 순서가 materialCode 보다 sellerCode 가 우선 인게 맞는지 의문? (롤업순서와 다름)
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 '
이게원본코드입니다..
저는 누가수정해놓은걸 바꾸려고했는데
원본에서 하는게 괜찮을까요?
정렬순서가
- stockinday, sellercode, materialName, materialCode 가 맞는지 의문
- 롤업 순서와 정렬 순서가 일치해야 합니다.
1안) 롤업 순서를 정렬 순서에 맞도록 바꾸던지
2안) 정렬 순서를 롤업 순서에 맞도록 바꾸던지
정렬순서는 맞습니다..
그럼 롤업순을 바꿔보겠습니다
1. 조회 항목 순서도 (sellerCode, sellerName) 을 (materialCode, materialName) 보다 앞에 두세요.
2. 소계 대상도 materialCode 만 있는데? sellerCode 별 소계도 필요한 건 아닌지?
3. 적용 방법
우선 SELECT 절의 소계 총계 표시를 위한 CASE 문을 없애고 ROLLUP 구문을 완성해 보세요
완성한 쿼리를 돌려보고 원하는 결과가 맞는지 확인하세요.
CASE 문은 그 다음에 추가.
답을 엄청 많이해주셔서 감사합니다!!!!
sellerCode 와 sellerName 은 한몸과 같으니 sellerName 을 앞으로 옮기면 좋을 듯 하고
sellerCode 의 정렬순서가 먼저이니 sellerCode 를 앞쪽으로 옮기는게 좋을 듯 합니다.
(sellerCode, sellerName, materialName, 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)) '
이렇게 했습니다 감사합니다
1. 위에 이미 설명 드렸는데요?
- 정렬할 때 알리아스가 아닌 원본 컬럼으로 정렬하기 위해 t. 을 붙여 줬고
- 정렬시 NULL 이 먼저 나오기 때문에 GROUPING 을 추가해 준 것입니다.
2. 해결한 쿼리를 공유해 주세요.
- rollup 부분
넵 해결했습니다.
ORDER BY GROUPING(t.stockinday ), t.stockinday
, GROUPING(t.sellercode ), t.sellercode
, GROUPING(t.materialName), t.materialName
, GROUPING(t.materialCode), t.materialCode
이 코드에 대한 설명이 있는 글이있을까요?
) 버튼을 클릭하여 작성 하시면 됩니다.