关于sql查询语句请教各位大神。

Question

StringBuffer sqlnumBuf = new StringBuffer(" SELECT COUNT(A.DJBH) FROM ( ");
sqlnumBuf.append(" SELECT E.XMBH,E.DJBH,E.DJLB,E.BDJE,E.CUSTNM,E.NAME1,E.LIFNR,E.BDYHJE,E.BDJSJE,E.DMMC,E.BRAND,E.DQZT,E.FKDH,T.FJE,T.FBL,T.FZT,T.FRQ,T.SJE,T.SBL,T.SZT,T.SRQ,T.TJE,T.TBL,T.TZT,T.TRQ,JS.WGBEZ,JS.PRDMANE,JS.PRDNO,JS.MODEL,JS.MEINS,JS.MENGE,JS.JSPRICE,JS.UPRICE,JS.QUMEMO,JS.JSTOTAL,JS.TOTAL,E.SSGS,T.FKZQS,E.ODTYPE,P.YLSJ, ROWNUM R");
sqlnumBuf.append(" FROM VIEW_CJJS E, JSLSFB JS, MAT_POITEM P,");
sqlnumBuf.append(" (SELECT E.DJBH,MAX(J.FKZQS) AS FKZQS, ");
sqlnumBuf.append(" MIN(DECODE(J.FKQS, '1', J.FKJE, '')) AS FJE, ");
sqlnumBuf.append(" MIN(DECODE(J.FKQS, '1', J.JSBL, '')) AS FBL, ");
sqlnumBuf.append(" MIN(DECODE(J.FKQS, '1', J.JSZT, '')) AS FZT, ");
sqlnumBuf.append(" MAX(DECODE(J.FKQS, '1', J.FKRQ, '')) AS FRQ, ");
sqlnumBuf.append(" MIN(DECODE(J.FKQS, '2', J.FKJE, '')) AS SJE, ");
sqlnumBuf.append(" MIN(DECODE(J.FKQS, '2', J.JSBL, '')) AS SBL, ");
sqlnumBuf.append(" MIN(DECODE(J.FKQS, '2', J.JSZT, '')) AS SZT, ");
sqlnumBuf.append(" MAX(DECODE(J.FKQS, '2', J.FKRQ, '')) AS SRQ, ");
sqlnumBuf.append(" MIN(DECODE(J.FKQS, '3', J.FKJE, '')) AS TJE, ");
sqlnumBuf.append(" MIN(DECODE(J.FKQS, '3', J.JSBL, '')) AS TBL, ");
sqlnumBuf.append(" MIN(DECODE(J.FKQS, '3', J.JSZT, '')) AS TZT, ");
sqlnumBuf.append(" MAX(DECODE(J.FKQS, '3', J.FKRQ, '')) AS TRQ ");

StringBuffer sqlselBuf = new StringBuffer(" SELECT D.*  FROM ( ");
sqlselBuf.append(" SELECT E.XMBH,E.DJBH,E.DJLB,E.BDJE,E.CUSTNM,E.NAME1,E.LIFNR,E.BDYHJE,E.BDJSJE,E.DMMC,E.BRAND,E.DQZT,E.FKDH,T.FJE,T.FBL,T.FZT,T.FRQ,T.SJE,T.SBL,T.SZT,T.SRQ,T.TJE,T.TBL,T.TZT,T.TRQ,JS.WGBEZ,JS.PRDMANE,JS.PRDNO,JS.MODEL,JS.MEINS,JS.MENGE,JS.JSPRICE,JS.UPRICE,JS.QUMEMO,JS.JSTOTAL,JS.TOTAL,E.SSGS,T.FKZQS,E.ODTYPE,P.YLSJ, ROWNUM R");
sqlselBuf.append(" FROM VIEW_CJJS E, JSLSFB JS, MAT_POITEM P,");
sqlselBuf.append(" (SELECT E.DJBH,MAX(J.FKZQS) AS FKZQS, ");
sqlselBuf.append(" MIN(DECODE(J.FKQS, '1', J.FKJE, '')) AS FJE, ");
sqlselBuf.append(" MIN(DECODE(J.FKQS, '1', J.JSBL, '')) AS FBL, ");
sqlselBuf.append(" MIN(DECODE(J.FKQS, '1', J.JSZT, '')) AS FZT, ");
sqlselBuf.append(" MAX(DECODE(J.FKQS, '1', J.FKRQ, '')) AS FRQ, ");
sqlselBuf.append(" MIN(DECODE(J.FKQS, '2', J.FKJE, '')) AS SJE, ");
sqlselBuf.append(" MIN(DECODE(J.FKQS, '2', J.JSBL, '')) AS SBL, ");
sqlselBuf.append(" MIN(DECODE(J.FKQS, '2', J.JSZT, '')) AS SZT, ");
sqlselBuf.append(" MAX(DECODE(J.FKQS, '2', J.FKRQ, '')) AS SRQ, ");
sqlselBuf.append(" MIN(DECODE(J.FKQS, '3', J.FKJE, '')) AS TJE, ");
sqlselBuf.append(" MIN(DECODE(J.FKQS, '3', J.JSBL, '')) AS TBL, ");
sqlselBuf.append(" MIN(DECODE(J.FKQS, '3', J.JSZT, '')) AS TZT, ");
sqlselBuf.append(" MAX(DECODE(J.FKQS, '3', J.FKRQ, '')) AS TRQ ");

StringBuffer sqlBuf = new StringBuffer("FROM VIEW_CJJS E, JSLSZB J, MAT_POITEM P ");
sqlBuf.append(" WHERE E.DJBH = J.DJBH AND E.DQZT <> '16' AND E.SSGSNO in ("+ssgsno+") ");
if(fkrq!=null && !fkrq.equals("")){
    sqlBuf.append(" AND J.FKRQ >= TO_DATE('"+fkrq+"', 'yyyy/MM/dd') ");
}
if(kfqs!=null && !kfqs.equals("")){
    sqlBuf.append(" AND J.FKQS LIKE '%"+kfqs+"%' ");
}

//分期付款
if(jsfs!=null && jsfs.equals("1")){
    sqlBuf.append(" AND J.JSBL = '1' ");
}else if(jsfs!=null && jsfs.equals("2")){
    sqlBuf.append(" AND J.JSBL <> '1' ");
}

sqlBuf.append(" GROUP BY E.DJBH ");

sqlnumBuf.append(sqlBuf);

sqlselBuf.append(sqlBuf);

sqlselBuf.append(" ) T ");
sqlnumBuf.append(" ) T ");
sqlselBuf.append(" WHERE E.DJBH = T.DJBH AND T.DJBH=JS.DJBH");
sqlnumBuf.append(" WHERE E.DJBH = T.DJBH AND T.DJBH=JS.DJBH ) A");       
sqlselBuf.append(" AND ROWNUM<="+maxres+") D  where D.R>"+firstres);

想问教各位大神，这种多表查询，如果再增加一个表进行查询，应该怎么写？
请教各位大神？

Answer 1

这个在网上搜索一下！ 这个在网上搜索一下！