데이터베이스 질문과 답변
디비는 오라클입니다.
디비의 내용을 이렇습니다.
| id | year | type | value |
| 101 | 2014 | 1 | 100 |
| 101 | 2015 | 2 | 200 |
| 102 | 2014 | 1 | 100 |
| 102 | 2015 | 1 | 100 |
| 103 | 2015 | 1 | 300 |
제가 하고 싶은건..
하나의 id의 type이 2014년과 2015년이 같은지 아닌지는 판별하고 싶은겁니다..
초보라 감이 잘 안오네요.. 도움부탁드립니다.
by jkson
[2016.04.04 17:58:43]
WITH T AS (SELECT '101' ID, '2014' YEAR, '1' TYPE, '100' VALUE FROM DUAL
UNION ALL
SELECT '101', '2015', '2', '200' FROM DUAL
UNION ALL
SELECT '102', '2014', '1', '100' FROM DUAL
UNION ALL
SELECT '102', '2015', '1', '100' FROM DUAL
UNION ALL
SELECT '103', '2015', '1', '300' FROM DUAL)
SELECT ID
FROM T
WHERE YEAR IN ('2014','2015')
GROUP BY ID
HAVING COUNT(DISTINCT TYPE) > 1 -- type이 2개 이상
--HAVING COUNT(DISTINCT TYPE) = 1 -- type이 동일
by 마농
[2016.04.04 18:02:42]
SELECT id
, MIN(DECODE(year, '2014', type)) type_2014
, MIN(DECODE(year, '2015', type)) type_2015
, DECODE( MIN(DECODE(year, '2014', type))
, MIN(DECODE(year, '2015', type))
, '일치', '불일치') result_1
FROM t
WHERE year IN ('2014', '2015')
GROUP BY id
;
by swlee
[2016.04.04 18:04:55]
WITH T (id, year, type, value) AS (
SELECT 101, 2014, 1, 100 FROM DUAL UNION ALL
SELECT 101, 2015, 2, 200 FROM DUAL UNION ALL
SELECT 102, 2014, 1, 100 FROM DUAL UNION ALL
SELECT 102, 2015, 1, 100 FROM DUAL UNION ALL
SELECT 103, 2015, 1, 300 FROM DUAL
)
SELECT ID
,"2014_TYPE","2014_VALUE"
,"2015_TYPE","2015_VALUE"
,DECODE("2014_TYPE","2015_TYPE",'일치','불일치') GB
FROM T
Pivot
(MIN(TYPE) TYPE,MIN(VALUE) AS VALUE For YEAR In (2014 AS "2014",2015 AS "2015"))
ORDER BY ID;
by 창조의날개
[2016.04.04 18:05:56]
WITH TT(id, year, type, value) AS (
SELECT 101, 2014, 1, 100 FROM DUAL UNION ALL
SELECT 101, 2015, 2, 200 FROM DUAL UNION ALL
SELECT 102, 2014, 1, 100 FROM DUAL UNION ALL
SELECT 102, 2015, 1, 100 FROM DUAL UNION ALL
SELECT 103, 2015, 1, 300 FROM DUAL
)
SELECT id, year, type, value
, DECODE(year||type,
LAG(year) OVER(ORDER BY id, year)+1 || LAG(type) OVER(ORDER BY id, year),
'일치',
LAG(year) OVER(ORDER BY id, year DESC)-1 || LAG(type) OVER(ORDER BY id, year DESC),
'일치',
'불일치') 비교
FROM TT
;
by 초초
[2016.04.04 19:06:42]
와 답변 달아주신분들 모두 정말 감사합니다..^^
) 버튼을 클릭하여 작성 하시면 됩니다.