데이터베이스 질문과 답변
그룹바이 부탁드려요~
0
3
1,225
by 현씨 [2012.12.14 18:39:10]
SELECT /*+ rule*/ a.BUSI_DEPT||'-'|| a.YYMM||'-'|| a.SEQ,
NVL(TO_CHAR(a.ACPT_DATE, 'yyyy/mm/dd'), ' ') ACPT_DATE,
NVL(TO_CHAR(a.ACCT_DATE, 'yyyy/mm/dd'), ' ') ACCT_DATE,
NVL(a.AGENT, ' ') AGENT,
NVL(a.PRDT_CODE, ' ') PRDT_CODE,
NVL(c.PRDT_NAME, ' ') prdt_name,
NVL(d.NAME, ' ') name,
NVL(d.ADDR, ' ') addr,
NVL(TO_CHAR(a.CHK_DECD_DATE, 'yyyy/mm/dd'), ' ') chk_decd_date,
NVL(TO_CHAR(a.sale_date, 'yyyy/mm/dd'), ' ') sale_date,
NVL(REPLACE(a.BAL_SANG_REASON,CHR(10),''), ' ') val_sang_reason,
NVL(a.DECD_SND, ' ') decd_snd,
NVL(a.gi_dae_no, ' ') gi_dae_no,
NVL(a.engi_no, ' ') engi_no,
NVL(e.PART_NO, ' ') PART_NO,
DECODE(SUBSTR(e.PART_NO,1,1)||SUBSTR(e.PART_NO,12,1),'P#',TO_CHAR(NVL(e.chgo_QTY, 0)*0.01,'99.99'),NVL(e.chgo_QTY, 0)) chgo_QTY,
NVL(e.EXCD_AMT, 0) EXCD_AMT,
NVL(e.CAUSE_GU,' ' ) CAUSE_GU,
NVL(e.CAUSE_GU1,' ') CAUSE_GU1,
NVL(TO_CHAR(e.chgo_date,'yy/mm/dd'),'**/**/**') chgo_date,
NVL(f.PART_NAME, ' ') PART_NAME,
NVL(g.HAJA_CONT, ' ') HAJA_CONT,
NVL(h.GE_RAE_NO, ' ') GE_RAE_NO
FROM CL_REPORT_MAST a, SC_PRODUCT c, CU_CUSTOM_MAST d, CL_FREE_REP_OUT e, SD_PART_MAST f, SD_FAULT_CODE g, AS_PART_OUTPUT h
WHERE a.busi_dept = e.CL_BUSI_DEPT(+)
AND a.yymm = e.CL_YYMM(+)
AND a.seq = e.CL_SEQ(+)
AND e.HAJA_CODE IS NOT NULL
AND a.BUSI_DEPT IN ('G','X')
AND a.AGENT LIKE :in_agent||'%'
AND a.DECD_SND != 'N'
AND a.BUSI_DEPT LIKE :in_busi_dept
AND a.acct_date BETWEEN TO_DATE(:f_date, 'yyyymmdd') AND TO_DATE(:t_date, 'yyyymmdd')
AND NVL(e.del_gu,' ') != 'Y'
AND e.PART_NO = f.PART_NO
AND a.PRDT_CODE = c.PRDT_CODE
AND a.CUSTOM_ID = d.CUSTOM_ID
AND a.PAY_UNPAY_GU = '2'
AND e.HAJA_CODE = g.HAJA_CODE(+)
AND e.chgo_busi_dept = h.busi_dept(+)
AND e.chgo_agent = h.agent(+)
AND e.chgo_yymm = h.yymm(+)
AND e.chgo_seq = h.seq(+)
AND e.chgo_seq_no = h.seq_no(+)
ORDER BY a.BUSI_DEPT, a.YYMM,
a.SEQ, a.ACPT_DATE,
a.AGENT;
요런게 있는데여~
간단하게 설명드리자면
| G-201212-0062 | 2012-12-03 | TK0W00003 | HA00000151A | 1 | 1000 |
| G-201212-0062 | 2012-12-03 | TK0W00003 | CQI3010000A3 | 1 | 500 |
| G-201212-0062 | 2012-12-03 | TK0W00003 | P0010000001# | 1 | 20 |
| G-201212-0062 | 2012-12-03 | TK0W00003 | P0010000002# | 1 | 10 |
part_no
| G-201212-0062 | 2012-12-03 | TK0W00003 | HA00000151A | 1 | 1000 |
| CQI3010000A3 | 1 | 500 | |||
| P0010000001# | 1 | 20 | |||
| P0010000002# | 1 | 10 |
요렇게 중복되는것들은 한번만 나오게 하고 싶습니다.
급하게 하려니 생각이 영 안나요~
부탁드려요~
by 오케클릭
[2012.12.15 03:28:05]
WITH t(part_no, dt, cd1, cd2, cnt, money) AS (
SELECT 'G-201212-0062','2012-03','TK0W00003','HA00000151A','1',1000 FROM dual UNION ALL
SELECT 'G-201212-0062','2012-03','TK0W00003','CQI3010000A3','1',500 FROM dual UNION ALL
SELECT 'G-201212-0062','2012-03','TK0W00003','P0010000001#','1',20 FROM dual UNION ALL
SELECT 'G-201212-0062','2012-03','TK0W00003','P0010000002#','1',10 FROM dual
)
SELECT CASE WHEN LAG(part_no,1) OVER (ORDER BY PART_NO, MONEY DESC) = part_no
THEN '' ELSE part_no END AS part_no
, CASE WHEN LAG(dt,1) OVER (ORDER BY PART_NO, MONEY DESC) = dt
THEN '' ELSE dt END AS dt
, CASE WHEN LAG(cd1,1) OVER (ORDER BY PART_NO, MONEY DESC) = cd1
THEN '' ELSE cd1 END AS cd1
, cd2, cnt, money FROM t
ORDER BY PART_NO, MONEY DESC
by 알콩
[2012.12.18 10:41:25]
-- 오케 님과 좀 다른 방법으로 해봤습니다.^^ WITH t(part_no, dt, cd1, cd2, cnt, money) AS ( SELECT 'G-201212-0062','2012-03','TK0W00003','HA00000151A','1',1000 FROM dual UNION ALL SELECT 'G-201212-0062','2012-03','TK0W00003','CQI3010000A3','1',500 FROM dual UNION ALL SELECT 'G-201212-0062','2012-03','TK0W00003','P0010000001#','1',20 FROM dual UNION ALL SELECT 'G-201212-0062','2012-03','TK0W00003','P0010000002#','1',10 FROM dual ) SELECT DECODE(ROW_NUMBER() OVER(PARTITION BY PART_NO, DT, CD1 ORDER BY ROWNUM), '1', PART_NO, NULL) AS PART_NO , DECODE(ROW_NUMBER() OVER(PARTITION BY PART_NO, DT, CD1 ORDER BY ROWNUM), '1', DT , NULL) AS DT , DECODE(ROW_NUMBER() OVER(PARTITION BY PART_NO, DT, CD1 ORDER BY ROWNUM), '1', CD1 , NULL) AS CD1 , CD2 , CNT , MONEY FROM T
by 알콩
[2012.12.18 10:42:45]
제가한건 원하시는 쿼리와 안맞을수도 있겠군요 ^^;;
) 버튼을 클릭하여 작성 하시면 됩니다.