SELECT F.NAM_FACT||'('||D.COD_FACT||')' NAM_FACT,UAINVM.DAT_INVM,D.DAT_BRTH,D.DAT_ARMM,D.NUM_ARMM,C1.CONTENT CLS_ARMM,U.SUMMARY,P.DSC_PAY COD_PAYM,D.COD_DOLA,D.MNY_ARMMNT,D.MNY_AMT + D.AMT_TAX MNY_AMT,D.MNY_RCV,UAINVM.NUM_INVM,D.NUM_PROD,C2.CONTENT STS_LINE,D.DPT_CTL
FROM DARMM D,CODD C1,CODD C2,PAYCD P,FACT F,UAVSUB U,UAINVM
WHERE D.dat_brth <= '20081231'
AND D.cod_fact=F.cod_fact
--and D.cod_fact ='S0000387'
and D.dat_brth >= '20081001' and D.dat_brth <= '20081231'
and D.dat_armm >= '20081001' and D.dat_armm <= '20081231'
and D.sts_line >= '20' and D.sts_line <= '20'
and C1.CODE_ID = 'DCLSARM' AND C1.CODE = D.CLS_ARMM
AND C2.CODE_ID = 'STSDARM' AND C2.CODE = D.STS_LINE
AND D.NUM_PROD = U.NUM_PROD
AND D.NUM_PROD = UAINVM.NUM_PROD
AND U.COD_DEP IS NOT NULL
AND D.COD_PAYM = P.COD_PAY(+)
AND D.CLS_ARMM <> 'B8'SELECT t.NUM_PROD NUM_PROD, MAX(substr(sys_connect_by_path(t.summary, '/'), 2)) SUMMARY
FROM (SELECT NUM_PROD, summary, row_number() over(PARTITION BY NUM_PROD ORDER BY summary) rn
FROM UAVSUB where NUM_PROD='0810280033'
and UAVSUB.COD_DEP IS NOT NULL) t
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1
AND NUM_PROD = PRIOR NUM_PRODGROUP BY t.NUM_PROD;大家好,兩段sql。第一段SQL是我要用到.net里做報表的,查詢的情況是NUM_PROD與SUMMARY為一對多,這樣造成我報表裡面有重複行好比:
NUM_PROD SUMMARY
1 A
1 B
這樣,我而我想顯示的效果是:
NUM_PROD SUMMARY
1 AB(或者A/B,中間有分隔符最好了)
第二段SQL是可以實現行合并的,但我有限制了特定的NUM_PROD(ps:NUM_PROD='0810280033'等這樣的條件)
我不知道怎么整合起來用。
請指教了
sql可能有些亂,麻煩能幫忙耐心看。
謝謝!!
FROM DARMM D,CODD C1,CODD C2,PAYCD P,FACT F,UAVSUB U,UAINVM
WHERE D.dat_brth <= '20081231'
AND D.cod_fact=F.cod_fact
--and D.cod_fact ='S0000387'
and D.dat_brth >= '20081001' and D.dat_brth <= '20081231'
and D.dat_armm >= '20081001' and D.dat_armm <= '20081231'
and D.sts_line >= '20' and D.sts_line <= '20'
and C1.CODE_ID = 'DCLSARM' AND C1.CODE = D.CLS_ARMM
AND C2.CODE_ID = 'STSDARM' AND C2.CODE = D.STS_LINE
AND D.NUM_PROD = U.NUM_PROD
AND D.NUM_PROD = UAINVM.NUM_PROD
AND U.COD_DEP IS NOT NULL
AND D.COD_PAYM = P.COD_PAY(+)
AND D.CLS_ARMM <> 'B8'SELECT t.NUM_PROD NUM_PROD, MAX(substr(sys_connect_by_path(t.summary, '/'), 2)) SUMMARY
FROM (SELECT NUM_PROD, summary, row_number() over(PARTITION BY NUM_PROD ORDER BY summary) rn
FROM UAVSUB where NUM_PROD='0810280033'
and UAVSUB.COD_DEP IS NOT NULL) t
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1
AND NUM_PROD = PRIOR NUM_PRODGROUP BY t.NUM_PROD;大家好,兩段sql。第一段SQL是我要用到.net里做報表的,查詢的情況是NUM_PROD與SUMMARY為一對多,這樣造成我報表裡面有重複行好比:
NUM_PROD SUMMARY
1 A
1 B
這樣,我而我想顯示的效果是:
NUM_PROD SUMMARY
1 AB(或者A/B,中間有分隔符最好了)
第二段SQL是可以實現行合并的,但我有限制了特定的NUM_PROD(ps:NUM_PROD='0810280033'等這樣的條件)
我不知道怎么整合起來用。
請指教了
sql可能有些亂,麻煩能幫忙耐心看。
謝謝!!
给你举个例子好了。NUM_PROD SUMMARY
1 A
1 B
-------------
select NUM_PROD,wmsys.wm_concat(summary) as SUMMARY from yourtable group by NUM_PROD
---结果
NUM_PROD SUMMARY
1 A,B
用sys_connect_by_path来实现
http://topic.csdn.net/u/20080505/11/a0958b42-d938-465f-972a-0f61a2969c97.html?seed=491226048
SELECT NAM_FACT,
DAT_INVM,
DAT_BRTH,
DAT_ARMM,
NUM_ARMM,
CLS_ARMM,
COD_PAYM,
COD_DOLA,
MNY_ARMMNT,
MNY_AMT,
MNY_RCV,
NUM_INVM,
STS_LINE,
DPT_CTL,
NUM_PROD,
MAX(SUBSTR(SYS_CONNECT_BY_PATH(SUMMARY, '/'), 2)) SUMMARY
FROM (SELECT F.NAM_FACT || '(' || D.COD_FACT || ')' NAM_FACT,
UAINVM.DAT_INVM DAT_INVM,
D.DAT_BRTH DAT_BRTH,
D.DAT_ARMM DAT_ARMM,
D.NUM_ARMM NUM_ARMM,
C1.CONTENT CLS_ARMM,
U.SUMMARY SUMMARY,
P.DSC_PAY COD_PAYM,
D.COD_DOLA COD_DOLA,
D.MNY_ARMMNT MNY_ARMMNT,
D.MNY_AMT + D.AMT_TAX MNY_AMT,
D.MNY_RCV MNY_RCV,
UAINVM.NUM_INVM NUM_INVM,
D.NUM_PROD NUM_PROD,
C2.CONTENT STS_LINE,
D.DPT_CTL DPT_CTL,
ROW_NUMBER() OVER(PARTITION BY F.NAM_FACT || '(' || D.COD_FACT || ')', UAINVM.DAT_INVM, D.DAT_BRTH, D.DAT_ARMM, D.NUM_ARMM, C1.CONTENT,
P.DSC_PAY, D.COD_DOLA, D.MNY_ARMMNT, D.MNY_AMT + D.AMT_TAX, D.MNY_RCV, UAINVM.NUM_INVM, C2.CONTENT, D.DPT_CTL, D.NUM_PROD ORDER BY SUMMARY) RN
FROM DARMM D, CODD C1, CODD C2, PAYCD P, FACT F, UAVSUB U, UAINVM
WHERE D.DAT_BRTH <= '20081231'
AND D.COD_FACT = F.COD_FACT
--AND D.COD_FACT ='S0000387'
AND D.DAT_BRTH >= '20081001'
AND D.DAT_BRTH <= '20081231'
AND D.DAT_ARMM >= '20081001'
AND D.DAT_ARMM <= '20081231'
AND D.STS_LINE >= '20'
AND D.STS_LINE <= '20'
AND C1.CODE_ID = 'DCLSARM'
AND C1.CODE = D.CLS_ARMM
AND C2.CODE_ID = 'STSDARM'
AND C2.CODE = D.STS_LINE
AND D.NUM_PROD = U.NUM_PROD
AND D.NUM_PROD = UAINVM.NUM_PROD
AND U.COD_DEP IS NOT NULL
AND D.COD_PAYM = P.COD_PAY(+)
AND D.CLS_ARMM <> 'B8'
AND NUM_PROD = '0810280033') TT
START WITH RN = 1
CONNECT BY RN = PRIOR RN + 1
AND NUM_PROD = PRIOR NUM_PROD
GROUP BY NAM_FACT,
DAT_INVM,
DAT_BRTH,
DAT_ARMM,
NUM_ARMM,
CLS_ARMM,
COD_PAYM,
COD_DOLA,
MNY_ARMMNT,
MNY_AMT,
MNY_RCV,
NUM_INVM,
STS_LINE,
DPT_CTL,
NUM_PROD;
COD_PAYM,COD_DOLA,MNY_ARMMNT,MNY_RCV,UNPAID,NUM_INVM,NUM_PROD,STS_LINE,DPT_CTL
FROM (
SELECT F.NAM_FACT||'('||D.COD_FACT||')' NAM_FACT,
UAINVM.DAT_INVM,
D.DAT_BRTH DAT_BRTH,
D.DAT_ARMM DAT_ARMM,
D.NUM_ARMM NUM_ARMM,
C1.CONTENT CLS_ARMM,
U.SUMMARY SUMMARY,
P.DSC_PAY COD_PAYM,
D.COD_DOLA COD_DOLA,
D.MNY_ARMMNT MNY_ARMMNT,
D.MNY_RCV MNY_RCV,
D.MNY_ARMMNT - D.MNY_RCV UNPAID,
UAINVM.NUM_INVM NUM_INVM,
D.NUM_PROD NUM_PROD,
C2.CONTENT STS_LINE,
D.DPT_CTL DPT_CTL,
ROW_NUMBER() OVER(PARTITION BY F.NAM_FACT || '(' || D.COD_FACT || ')', UAINVM.DAT_INVM, D.DAT_BRTH, D.DAT_ARMM, D.NUM_ARMM, C1.CONTENT,
P.DSC_PAY, D.COD_DOLA, D.MNY_ARMMNT, D.MNY_AMT + D.AMT_TAX, D.MNY_RCV, UAINVM.NUM_INVM, C2.CONTENT, D.DPT_CTL, D.NUM_PROD ORDER BY SUMMARY) RN
FROM DARMM D, CODD C1, CODD C2, PAYCD P, FACT F, UAVSUB U, UAINVM
WHERE D.DAT_BRTH <= '20081231'
AND D.COD_FACT = F.COD_FACT
AND D.DAT_BRTH >= '20081001'
AND D.DAT_BRTH <= '20081231'
AND D.DAT_ARMM >= '20081001'
AND D.DAT_ARMM <= '20081231'
AND D.STS_LINE >= '20'
AND D.STS_LINE <= '20'
AND C1.CODE_ID = 'DCLSARM'
AND C1.CODE = D.CLS_ARMM
AND C2.CODE_ID = 'STSDARM'
AND C2.CODE = D.STS_LINE
AND D.NUM_PROD = U.NUM_PROD
AND D.NUM_PROD = UAINVM.NUM_PROD(+)
AND (U.DORC='D' OR U.COD_DEP IS NOT NULL)
AND D.COD_PAYM = P.COD_PAY(+)
AND D.CLS_ARMM <> 'B8'
) TT
START WITH RN = 1
CONNECT BY RN = PRIOR RN + 1
AND NUM_PROD = PRIOR NUM_PROD GROUP BY NAM_FACT,DAT_INVM,DAT_BRTH,DAT_ARMM,NUM_ARMM,CLS_ARMM,
COD_PAYM,COD_DOLA,MNY_ARMMNT,MNY_RCV,UNPAID,NUM_INVM,NUM_PROD,STS_LINE,DPT_CTL;
謝謝mantisXF的幫忙!
我改過這後的sql可以查“AND D.DAT_BRTH >= '20081001'
AND D.DAT_BRTH <= '20081231'
AND D.DAT_ARMM >= '20081001'
AND D.DAT_ARMM <= '20081231'”
這個區間的資料沒有問題
但放大區間到“AND D.DAT_BRTH >= '20080501'
AND D.DAT_BRTH <= '20081231'
AND D.DAT_ARMM >= '20080501'
AND D.DAT_ARMM <= '20081231'”
之後跳出下面的錯誤提示,麻煩再幫忙了,謝謝!
ORA-30004: when using SYS_CONNECT_BY_PATH function, cannot have seperator as part of column value
http://www.blogjava.net/leekiang/archive/2007/10/31/157309.html?opt=admin