2 twyx0621 twyx0621 于 2015.05.27 16:09 提问

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

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);

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

3个回答

liuchong9
liuchong9   2015.05.27 16:16

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

qaq123147
qaq123147   2015.05.27 16:18

如果那几个表有关联就嵌套查询。select xx from xx where xx=(select xx from xx where xx=xx);

twyx0621
twyx0621 最主要加的这个新表没有关联,最主要这个项目是接别人的, 对查询这块还不太熟
接近 3 年之前 回复
qq_26559297
qq_26559297   2015.05.27 16:22

什么数据库?对应位置加表并且关联上就行了

twyx0621
twyx0621 回复qq_26559297: 你好,你有QQ吗,
接近 3 年之前 回复
qq_26559297
qq_26559297 回复twyx0621: 客气
接近 3 年之前 回复
twyx0621
twyx0621 嗯,谢谢,我这里先试一下。
接近 3 年之前 回复
qq_26559297
qq_26559297 回复twyx0621: 业务报表你不关联能随便查么。。稳妥点加个外键什么的
接近 3 年之前 回复
qq_26559297
qq_26559297 回复twyx0621: select * from tablea left join tableb on 1=1
接近 3 年之前 回复
qq_26559297
qq_26559297 回复twyx0621: 没有关联还要加上的话可以这样写
接近 3 年之前 回复
twyx0621
twyx0621 oracle 数据库
接近 3 年之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!