데이터베이스 질문과 답변
by 클라나드 [2014.04.29 15:30:40]
테이블은 카드 테이블, 포인트내역 테이블이있습니다.
매장별 카드등록수와 포인트적립 계를 구하려고 합니다.
WITH CARD AS
( SELECT '마농' AS NM, '수퍼' AS STOR, 'Y' AS REGYN FROM DUAL UNION ALL
SELECT '부쉬맨' AS NM, '수퍼' AS STOR, 'N' AS REGYN FROM DUAL UNION ALL
SELECT '우리집아찌' AS NM, '마트' AS STOR, 'N' AS REGYN FROM DUAL UNION ALL
SELECT '아발란체' AS NM, '마트' AS STOR, 'Y' AS REGYN FROM DUAL
)
SELECT * FROM CARD
마농 수퍼 Y
부쉬맨 수퍼 N
우리집아찌 마트 N
아발란체 마트 Y
카드 테이블에는 4명이 등록되었습니다.
하지만 등록 완료된건 마농님과 아발란체님 2명입니다.
WITH PT AS
( SELECT 30 as PT, '수퍼' AS STOR FROM DUAL UNION ALL
SELECT 10 as PT, '수퍼' AS STOR FROM DUAL UNION ALL
SELECT 20 as PT, '수퍼' AS STOR FROM DUAL UNION ALL
SELECT 50 as PT, '마트' AS STOR FROM DUAL UNION ALL
SELECT -10 as PT, '마트' AS STOR FROM DUAL UNION ALL
SELECT -20 as PT, '마트' AS STOR FROM DUAL UNION ALL
SELECT -40 as PT, '수퍼' AS STOR FROM DUAL
)
SELECT * FROM PT
포인트 테이블에있는내용입니다.
매장에서 적립량과 사용량이 있습니다.
30 수퍼
10 수퍼
20 수퍼
50 마트
-10 마트
-20 마트
-40 수퍼
WITH TOTAL AS
( SELECT '수퍼' AS STOR, --매장이름
2 AS TOTALCARD, --총카드수
1 AS REGY, --등록된카드수(REGYN = Y)
1 AS REGN, --미등록된카드수(REGYN = N)
60 AS SAVE_POINT, -- 적립포인트
-40 AS USE_POINT -- 사용포인트
FROM DUAL UNION ALL
SELECT '마트' AS STOR, 2 AS TOTALCARD, 1 AS REGY, 1 AS REGN, 50 AS SAVE_POINT, -70 AS USE_POINT FROM DUAL
)
SELECT * FROM TOTAL
매장 총카드수 등록카드 미등록카드 총적립포인트 총사용포인트
수퍼 2 1 1 60 -40
마트 2 1 1 50 -70
제가 원하는 데이터입니다.
매장에서 등록된 총카드수와 등록된카드 미등록된카드 적립포인트 사용포인트의 계를 원하고있습니다.
작업한지 1주일이 넘었습니다..
고수님들의 도움을 부탁드립니다...
너무 힘드네요ㅠㅠ
어려운글 읽어주셔서 감사합니다.
WITH CARD AS
( SELECT '마농' AS NM, '수퍼' AS STOR, 'Y' AS REGYN FROM DUAL UNION ALL
SELECT '부쉬맨' AS NM, '수퍼' AS STOR, 'N' AS REGYN FROM DUAL UNION ALL
SELECT '우리집아찌' AS NM, '마트' AS STOR, 'N' AS REGYN FROM DUAL UNION ALL
SELECT '아발란체' AS NM, '마트' AS STOR, 'Y' AS REGYN FROM DUAL
),
PT AS
( SELECT 30 as PT, '수퍼' AS STOR FROM DUAL UNION ALL
SELECT 10 as PT, '수퍼' AS STOR FROM DUAL UNION ALL
SELECT 20 as PT, '수퍼' AS STOR FROM DUAL UNION ALL
SELECT 50 as PT, '마트' AS STOR FROM DUAL UNION ALL
SELECT -10 as PT, '마트' AS STOR FROM DUAL UNION ALL
SELECT -20 as PT, '마트' AS STOR FROM DUAL UNION ALL
SELECT -40 as PT, '수퍼' AS STOR FROM DUAL
)
SELECT B.STOR,
A.CNT,
A.A_CARD,
A.B_CARD,
B.A_PT,
B.B_PT
FROM ( SELECT A.STOR,
COUNT(*) AS CNT,
SUM(CASE WHEN A.REGYN = 'Y' THEN 1 END) AS A_CARD,
SUM(CASE WHEN A.REGYN = 'N' THEN 1 END) AS B_CARD
FROM CARD A
GROUP BY A.STOR) A,
( SELECT B.STOR,
SUM(CASE WHEN B.PT > 0 THEN B.PT END) AS A_PT,
SUM(CASE WHEN B.PT < 0 THEN B.PT END) AS B_PT
FROM PT B
GROUP BY B.STOR) B
WHERE A.STOR = B.STOR;
헐 긴글 읽어주시고
답변감사합니다
제 별칭은 없군요. 열심히 노력해야 겠다는 생각이 드네요.. ^^
WITH CARD AS ( SELECT '마농' AS NM, '수퍼' AS STOR, 'Y' AS REGYN FROM DUAL UNION ALL SELECT '부쉬맨' AS NM, '수퍼' AS STOR, 'N' AS REGYN FROM DUAL UNION ALL SELECT '우리집아찌' AS NM, '마트' AS STOR, 'N' AS REGYN FROM DUAL UNION ALL SELECT '아발란체' AS NM, '마트' AS STOR, 'Y' AS REGYN FROM DUAL ) , PT AS ( SELECT 30 as PT, '수퍼' AS STOR FROM DUAL UNION ALL SELECT 10 as PT, '수퍼' AS STOR FROM DUAL UNION ALL SELECT 20 as PT, '수퍼' AS STOR FROM DUAL UNION ALL SELECT 50 as PT, '마트' AS STOR FROM DUAL UNION ALL SELECT -10 as PT, '마트' AS STOR FROM DUAL UNION ALL SELECT -20 as PT, '마트' AS STOR FROM DUAL UNION ALL SELECT -40 as PT, '수퍼' AS STOR FROM DUAL ) SELECT A.STOR, A.총카드수, 미등록카드 , SUM(DECODE(SIGN(PT), 1, PT)) AS 총적립포인트 , SUM(DECODE(SIGN(PT), -1, PT)) AS 총사용포인트 FROM ( SELECT A.STOR , COUNT(A.STOR) AS 총카드수 , COUNT(DECODE(REGYN, 'Y', 1)) AS 등록카드 , COUNT(DECODE(REGYN, 'N', 1)) AS 미등록카드 FROM CARD A GROUP BY A.STOR ) A , PT B WHERE A.STOR = B.STOR GROUP BY A.STOR, A.총카드수, 미등록카드 ;
다음에 또질문이생기면 필히 농부지기님 올리겠습니다!!
답변 감사드립니다^^
별칭때문에 상처 받음 ㅋㅋ
후잉 상처받지 마시옵소서~ㅠㅠ
^^
상처는 아니구요... 그냥 ~~
아직 레벨이 초보라서 열심히 노력 해야죠..
with card as(
select '마농' as nm, '수퍼' as stor, 'Y' as regyn from dual union all
select '부쉬맨' as nm, '수퍼' as stor, 'N' as regyn from dual union all
select '우리집아찌' as nm, '마트' as stor, 'N' as regyn from dual union all
select '아발란체' as nm, '마트' as stor, 'Y' as regyn from dual
), pt as(
select 30 as pt, '수퍼' as stor from dual union all
select 10 as pt, '수퍼' as stor from dual union all
select 20 as pt, '수퍼' as stor from dual union all
select 50 as pt, '마트' as stor from dual union all
select -10 as pt, '마트' as stor from dual union all
select -20 as pt, '마트' as stor from dual union all
select -40 as pt, '수퍼' as stor from dual
)
select a.stor "매장",
tcard "총카드수",
ycard "등록카드",
ncard "미등록카드",
sumpt "총적립포인트",
usept "총사용포인트"
from (select stor,
count(1) tcard,
count(decode(regyn, 'Y', 1)) ycard,
count(decode(regyn, 'N', 1)) ncard
from card
group by stor) a,
(select stor,
sumpt,
sum(usept) over(order by nullif(stor, '마트') nulls last) usept
from (select stor,
sum(decode(sign(pt), 1, pt)) sumpt,
sum(decode(sign(pt), -1, pt)) usept
from pt
group by stor)) b
where a.stor = b.stor
) 버튼을 클릭하여 작성 하시면 됩니다.