MS-SQL] 코드트리 + 상세내역에 코드별 합계쿼리.

느룡...

 

좀더 빠른 쿼리는 없을까낭...

 

쿼리 속도가  빨라짐!!  장실에서 일보다 생각났음 ㅡ.,ㅡv

테이블 관계를 다시 확인하고, 가공해야 할 최소단위 데이타로 만들어서

58000여건에 대해 쿼리문을 적용하니 40sec -> 2sec 줄었음.

 

결과

 

 

 

;WITH CBS AS (
 SELECT *
 FROM (
 
  VALUES
 
  ('A', '코드A', '', 1),
  ('A.01', '코드A1', 'A', 2),
  ('A.02', '코드A2', 'A', 2),
  ('A.01.01', '코드A01', 'A.01', 3),
  ('A.01.02', '코드A02', 'A.02', 3)
 
 ) AS CBS ( CODE , CODENAME,  UPPER_CODE, CODE_LEVEL)
),
DETAIL AS
(
 SELECT *
 FROM (
  VALUES
 
  ('0001', '코드A.01.01.0001', 'A.01.01',  10),
  ('0002', '코드A.01.01.0002', 'A.01.01',  20),
  ('0003', '코드A.01.02.0003', 'A.01.02',  30),
  ('0004', '코드A.01.02.0004', 'A.01.02',  40),
  ('0005', '코드A.01.02.0005', 'A.01.02',  50)
 ) AS CBS ( CODE , CODENAME,  CBS_CODE, QUANTITY )
)
,
TOTAL AS
(
 SELECT SUBSTRING( CBS_CODE+ '.', 0, CHARINDEX( '.', CBS_CODE + '.')) AS CODE1 ,
   SUBSTRING( cbs_code+ '.', 0, CHARINDEX( '.', cbs_code + '.', CHARINDEX( '.', cbs_code+ '.')+1) ) as code2,
   CBS_CODE AS CODE3,
   CODE AS CODE4,
   CODENAME,
   QUANTITY  
 FROM DETAIL
)
 
SELECT CODE1 , 
  '' CODE_NAME,
  SUM(QUANTITY),
  CODE1 + '.00.00' AS ALIGN
FROM TOTAL t inner join CBS c
on  t.CODE1 = c.CODE and c.CODE_LEVEL = 1
GROUP BY CODE1

UNION ALL 
 
SELECT CODE2,
  '' CODE_NAME,
  SUM(QUANTITY)  ,
  CODE2 + '.00' AS ALIGN
FROM TOTAL t inner join CBS c
on  t.CODE2 = c.CODE and c.CODE_LEVEL = 2
GROUP BY  CODE1, CODE2
 
UNION ALL 
 
SELECT CODE3, 
  '' CODE_NAME,
  SUM(QUANTITY)  ,
  CODE3 AS ALIGN
FROM TOTAL t inner join CBS c
on  t.CODE3 = c.CODE and c.CODE_LEVEL = 3
GROUP BY CODE1, CODE2, CODE3
  
UNION ALL 
 
SELECT CODE4,
  CODENAME,
  SUM(QUANTITY) ,
  CODE3 + CODE4 AS ALIGN 
FROM TOTAL
GROUP BY CODE1,
  CODE2,
  CODE3,
  CODE4,
  CODENAME
 
ORDER BY ALIGN