데이터베이스 질문과 답변

멍청한 질문 드려서 죄송해요. 도와주세용. 0 5 2,268

by 버츠 [SQL Query] GROUP BY SQL [2018.03.30 13:19:10] 

SELECT *
  FROM (
        SELECT '001' AS ANO, '002' AS VNO, 220  AS CNT FROM DUAL UNION ALL
        SELECT '002' AS ANO, '001' AS VNO, 140  AS CNT FROM DUAL UNION ALL
        SELECT '003' AS ANO, '004' AS VNO, 2220 AS CNT FROM DUAL UNION ALL
        SELECT '004' AS ANO, '003' AS VNO, 2140 AS CNT FROM DUAL
       )
       
ANO VNO CNT        
--- --- -----------
001 002         220
002 001         140
003 004        2220
004 003        2140

위와 같은 데이터에서
아래와 같은 데이터를 뽑을 수 있을까요?

ANOVNO VNOANO CNT
------ ------ -----
001002 002001   360
003004 004003  4360

001002 는 ANO || VNO 로 합쳐 놓은겁니다.

 

by 우리집아찌 [2018.03.30 13:44:09] 
WITH T AS (
        SELECT '001' AS ANO, '002' AS VNO, 220  AS CNT FROM DUAL UNION ALL
        SELECT '002' AS ANO, '001' AS VNO, 140  AS CNT FROM DUAL UNION ALL
        SELECT '003' AS ANO, '004' AS VNO, 2220 AS CNT FROM DUAL UNION ALL
        SELECT '004' AS ANO, '003' AS VNO, 2140 AS CNT FROM DUAL
      
) 

SELECT A.V1 , A.V2 , ( A.CNT + B.CNT ) AS CNT
 FROM (SELECT ANO || VNO AS V1 , VNO || ANO AS V2 , CNT FROM T ) A
    , (SELECT ANO || VNO AS V1 , VNO || ANO AS V2 , CNT FROM T ) B
 WHERE A.V1 = B.V2
   AND B.V2 = A.V1
   AND A.V1 >= B.V1

 

by 예술은길다 [2018.03.30 13:46:14] 
WITH T AS (
           SELECT '001' AS ANO, '002' AS VNO, 220  AS CNT FROM DUAL UNION ALL
           SELECT '002' AS ANO, '001' AS VNO, 140  AS CNT FROM DUAL UNION ALL
           SELECT '003' AS ANO, '004' AS VNO, 2220 AS CNT FROM DUAL UNION ALL
           SELECT '004' AS ANO, '003' AS VNO, 2140 AS CNT FROM DUAL
)

SELECT ANOVNO,
       VNOANO,
       SUM(CNT) CNT
  FROM (SELECT MIN(ANO||VNO) OVER(PARTITION BY BASIS) ANOVNO,
               MAX(ANO||VNO) OVER(PARTITION BY BASIS) VNOANO,
               CNT
          FROM (SELECT LEAST(ANO, VNO) BASIS,
                       ANO,
                       VNO,
                       CNT
                  FROM T
               )
       )
 GROUP BY ANOVNO,
          VNOANO

 

by 버츠 [2018.03.30 13:47:00]

감사합니다.ㅠㅠ

by 마농 [2018.03.30 13:59:13] 
WITH t AS
(
SELECT '001' ano, '002' vno, 220 cnt FROM dual 
UNION ALL SELECT '002', '001',  140 FROM dual 
UNION ALL SELECT '003', '004', 2220 FROM dual 
UNION ALL SELECT '004', '003', 2140 FROM dual
)
SELECT ano||vno anovno
     , vno||ano vnoano
     , SUM(cnt) cnt
  FROM (SELECT LEAST   (ano, vno) ano
             , GREATEST(ano, vno) vno
             , cnt
          FROM t
        )
 GROUP BY ano, vno
 ORDER BY ano, vno
;

 

by 버츠 [2018.03.30 14:03:22]

LEAST, GREATEST 라는 함수를 이용하면 되는거였군요.
이 함수를 쓸 생각을 전혀 안했네요.


우리집 아찌님, 예술은 길다님, 마농님 감사합니다.

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