데이터베이스 질문과 답변

mssql 함수 질문 0 3 3,878

by 웅아 [2024.06.24 12:45:52]

버전 : mssql 2019

 

첨부한 이미지와 같이 함수로 해결할 수 있는 방법이 있을까요?

 

기초재고를 구분값으로 1월은 기초재고 2~5월은 기말재고 1~4월을 찍는 단순한 방법도 있는대

 

함수로 할 수 있는 방법이 있을까 해서요

 

by 마농 [2024.06.24 13:36:57]

원본 테이블 형태에 따라 구현 방법은 다를 수 있습니다.
보통 SUM() OVER() 분석함수를 이용해 누적 합계를 구할 수 있습니다.
올려주신 결과표를 구현하려면 행열전환 (피벗/언피벗) 기능도 이용해야 할 것 같네요.
기초재고가 별도 테이블로 되어 있다면? 조인도 필요하겠구요.
원본 테이블 예시자료를 보여주세요.
원본 대비 결과표를 보여주세요.
사용하신 쿼리를 보여주시면 좋구요.

by 알칸펠 [2024.07.17 11:51:10]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
WITH M AS (
    SELECT 1 MON, 25697 AS A, 45180 AS B, 44741 AS C, 26136 AS D FROM DUAL UNION ALL
    SELECT 2, NULL, 30774, 29024, 1750 FROM DUAL UNION ALL
   SELECT 3, NULL, 44789, 48517, 3728 FROM DUAL UNION ALL
   SELECT 4, NULL, 45835, 42150, 3685 FROM DUAL UNION ALL
   SELECT 5, NULL, 142204, 106020, 36184 FROM DUAL UNION ALL
   SELECT 6, NULL, NULL, NULL, NULL FROM DUAL
 ORDER BY MON
)
    SELECT '기초재고'
         , MIN(CASE MON WHEN 1 THEN A END) '1Mon'
         , MIN(CASE MON WHEN 1 THEN D END) '2Mon'
         , MIN(CASE MON WHEN 2 THEN D END) '3Mon'
         , MIN(CASE MON WHEN 3 THEN D END) '4Mon'
         , MIN(CASE MON WHEN 4 THEN D END) '5Mon'
         , MIN(CASE MON WHEN 5 THEN D END) '6Mon'
      FROM M
UNION ALL          
   SELECT '매입수량'
         , MIN(CASE MON WHEN 1 THEN B END) '1Mon'
         , MIN(CASE MON WHEN 2 THEN b END) '2Mon'
         , MIN(CASE MON WHEN 3 THEN b END) '3Mon'
         , MIN(CASE MON WHEN 4 THEN b END) '4Mon'
         , MIN(CASE MON WHEN 5 THEN b END) '5Mon'
         , MIN(CASE MON WHEN 6 THEN b END) '6Mon'
      FROM M
UNION ALL          
   SELECT '매출수량'
         , MIN(CASE MON WHEN 1 THEN c END) '1Mon'
         , MIN(CASE MON WHEN 2 THEN c END) '2Mon'
         , MIN(CASE MON WHEN 3 THEN c END) '3Mon'
         , MIN(CASE MON WHEN 4 THEN c END) '4Mon'
         , MIN(CASE MON WHEN 5 THEN c END) '5Mon'
         , MIN(CASE MON WHEN 6 THEN c END) '6Mon'
      FROM M
UNION ALL          
   SELECT '기말재고'
         , MIN(CASE MON WHEN 1 THEN d END) '1Mon'
         , MIN(CASE MON WHEN 2 THEN d END) '2Mon'
         , MIN(CASE MON WHEN 3 THEN d END) '3Mon'
         , MIN(CASE MON WHEN 4 THEN d END) '4Mon'
         , MIN(CASE MON WHEN 5 THEN d END) '5Mon'
         , MIN(CASE MON WHEN 6 THEN d END) '6Mon'
      FROM M

 

by 알칸펠 [2024.07.17 12:21:53]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
WITH M AS (
    SELECT '기초재고' AS TYP_NM, 'A' AS TYP, 25697 AS JAN, NULL AS FEB, NULL AS MAR, NULL AS APR, NULL AS MAY, NULL AS JUN FROM DUAL UNION ALL
    SELECT '매입수량' AS TYP_NM, 'B' AS TYP, 45180 AS JAN, 30774 AS FEB, 44789 AS MAR, 45835 AS APR, 142204 AS MAY, NULL AS JUN FROM DUAL UNION ALL
    SELECT '매출수량' AS TYP_NM, 'C' AS TYP, 44741 AS JAN, 29024 AS FEB, 48517 AS MAR, 42150 AS APR, 106020 AS MAY, NULL AS JUN FROM DUAL UNION ALL
    SELECT '기말재고' AS TYP_NM, 'D' AS TYP, 26136 AS JAN, 1750 AS FEB, 3728 AS MAR, 3685 AS APR, 36184 AS MAY, NULL AS JUN FROM DUAL
)
    SELECT TYP_NM
         , TYP
         , JAN
         , CASE TYP WHEN 'A' THEN LEAD(JAN, 3) OVER (ORDER BY TYP)
                    ELSE FEB
             END AS FEB
         , CASE TYP WHEN 'A' THEN LEAD(FEB, 3) OVER (ORDER BY TYP)
                    ELSE MAR
             END AS MAR
         , CASE TYP WHEN 'A' THEN LEAD(MAR, 3) OVER (ORDER BY TYP)
                    ELSE APR
             END AS APR
         , CASE TYP WHEN 'A' THEN LEAD(APR, 3) OVER (ORDER BY TYP)
                    ELSE MAY
             END AS MAY
         , CASE TYP WHEN 'A' THEN LEAD(MAY, 3) OVER (ORDER BY TYP)
                    ELSE JUN
             END AS JUN
      FROM M

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입