是是字符串的构造。但要判断该不该加where,该不该加and,加在什么地方。 strSQL可以是简单的如"select * from tablename" 也可以是: select * from dd_produce_journal,(select dd_pj_no, max(decode(forecast_time,'01:00',forecast_burden)) forecast_burden1, max(decode(forecast_time,'01:00',real_burden))real_burden1, max(decode(forecast_time,'02:00',forecast_burden))forecast_burden2, max(decode(forecast_time,'02:00',real_burden))real_burden2, max(decode(forecast_time,'03:00',forecast_burden))forecast_burden3, max(decode(forecast_time,'03:00',real_burden))real_burden3, max(decode(forecast_time,'04:00',forecast_burden)) forecast_burden4, max(decode(forecast_time,'04:00',real_burden))real_burden4, max(decode(forecast_time,'05:00',forecast_burden))forecast_burden5, max(decode(forecast_time,'05:00',real_burden))real_burden5, max(decode(forecast_time,'06:00',forecast_burden))forecast_burden6, max(decode(forecast_time,'06:00',real_burden))real_burden6, max(decode(forecast_time,'07:00',forecast_burden))forecast_burden7, max(decode(forecast_time,'07:00',real_burden))real_burden7, max(decode(forecast_time,'08:00',forecast_burden))forecast_burden8, max(decode(forecast_time,'08:00',real_burden))real_burden8, max(decode(forecast_time,'09:00',forecast_burden))forecast_burden9, max(decode(forecast_time,'09:00',real_burden))real_burden9, max(decode(forecast_time,'10:00',forecast_burden))forecast_burden10, max(decode(forecast_time,'10:00',real_burden))real_burden10, max(decode(forecast_time,'11:00',forecast_burden))forecast_burden11, max(decode(forecast_time,'11:00',real_burden))real_burden11, max(decode(forecast_time,'12:00',forecast_burden))forecast_burden12, max(decode(forecast_time,'12:00',real_burden))real_burden12, max(decode(forecast_time,'13:00',forecast_burden))forecast_burden13, max(decode(forecast_time,'13:00',real_burden)) real_burden13, max(decode(forecast_time,'14:00',forecast_burden))forecast_burden14, max(decode(forecast_time,'14:00',real_burden))real_burden14, max(decode(forecast_time,'15:00',forecast_burden))forecast_burden15, max(decode(forecast_time,'15:00',real_burden))real_burden15, max(decode(forecast_time,'16:00',forecast_burden))forecast_burden16, max(decode(forecast_time,'16:00',real_burden))real_burden16, max(decode(forecast_time,'17:00',forecast_burden))forecast_burden17, max(decode(forecast_time,'17:00',real_burden))real_burden17, max(decode(forecast_time,'18:00',forecast_burden))forecast_burden18, max(decode(forecast_time,'18:00',real_burden))real_burden18, max(decode(forecast_time,'19:00',forecast_burden))forecast_burden19, max(decode(forecast_time,'19:00',real_burden))real_burden19, max(decode(forecast_time,'20:00',forecast_burden))forecast_burden20, max(decode(forecast_time,'20:00',real_burden))real_burden20, max(decode(forecast_time,'21:00',forecast_burden))forecast_burden21, max(decode(forecast_time,'21:00',real_burden))real_burden21, max(decode(forecast_time,'22:00',forecast_burden))forecast_burden22, max(decode(forecast_time,'22:00',real_burden))real_burden22, max(decode(forecast_time,'23:00',forecast_burden))forecast_burden23, max(decode(forecast_time,'23:00',real_burden))real_burden23, max(decode(forecast_time,'24:00',forecast_burden))forecast_burden24, max(decode(forecast_time,'24:00',real_burden))real_burden24 from dd_dd_forecast_dayreport group by dd_pj_no)b where dd_produce_journal.dd_pj_no=b.dd_pj_no 或者:select t1.dd_rm_date,t1.station_name as station_name_l,t1.line_name as line_name_l,t1.dd_rm_runstate_morning as dd_rm_runstate_morning_l,t1.dd_rm_runstate_noon as dd_rm_runstate_noon_l,t1.dd_rm_runstate_night as dd_rm_runstate_night_l,t2.station_name as station_name_r,t2.line_name as line_name_r,t2.dd_rm_runstate_morning as dd_rm_runstate_morning_r,t2.dd_rm_runstate_noon as dd_rm_runstate_noon_r,t2.dd_rm_runstate_night as dd_rm_runstate_night_r from (select * from (select (select count(*)+1 from (select a.dd_rm_id,a.DD_RM_DATE,a.TRANSFORMERSUBSTATIONID as station_name,a.DD_RM_ROUTENAME as line_name,a.DD_RM_RUNSTATE as dd_rm_runstate_morning,b.DD_RM_RUNSTATE as dd_rm_runstate_noon,c.DD_RM_RUNSTATE as dd_rm_runstate_night from dd_running_mode a,dd_running_mode b,dd_running_mode c where a.transformersubstationid=b.transformersubstationid and b.transformersubstationid=c.transformersubstationid and a.dd_rm_routename=b.dd_rm_routename and b.dd_rm_routename=c.dd_rm_routename and a.dd_rm_date=b.dd_rm_date and b.dd_rm_date=c.dd_rm_date and a.dd_rm_classmode='白班' and b.dd_rm_classmode='中班' and c.dd_rm_classmode='夜班') tem_tbl1 where tem_tbl1.dd_rm_id<a.dd_rm_id) as rec_no,a.* from (select a.dd_rm_id,a.DD_RM_DATE,a.TRANSFORMERSUBSTATIONID as station_name,a.DD_RM_ROUTENAME as line_name,a.DD_RM_RUNSTATE as dd_rm_runstate_morning,b.DD_RM_RUNSTATE as dd_rm_runstate_noon,c.DD_RM_RUNSTATE as dd_rm_runstate_night from dd_running_mode a,dd_running_mode b,dd_running_mode c where a.transformersubstationid=b.transformersubstationid and b.transformersubstationid=c.transformersubstationid and a.dd_rm_routename=b.dd_rm_routename and b.dd_rm_routename=c.dd_rm_routename and a.dd_rm_date=b.dd_rm_date and b.dd_rm_date=c.dd_rm_date and a.dd_rm_classmode='白班' and b.dd_rm_classmode='中班' and c.dd_rm_classmode='夜班') a) b where mod(b.rec_no,2)=1) t1 left outer join (select * from (select (select count(*)+1 from (select a.dd_rm_id,a.DD_RM_DATE,a.TRANSFORMERSUBSTATIONID as station_name,a.DD_RM_ROUTENAME as line_name,a.DD_RM_RUNSTATE as dd_rm_runstate_morning,b.DD_RM_RUNSTATE as dd_rm_runstate_noon,c.DD_RM_RUNSTATE as dd_rm_runstate_night from dd_running_mode a,dd_running_mode b,dd_running_mode c where a.transformersubstationid=b.transformersubstationid and b.transformersubstationid=c.transformersubstationid and a.dd_rm_routename=b.dd_rm_routename and b.dd_rm_routename=c.dd_rm_routename and a.dd_rm_date=b.dd_rm_date and b.dd_rm_date=c.dd_rm_date and a.dd_rm_classmode='白班' and b.dd_rm_classmode='中班' and c.dd_rm_classmode='夜班') tem_tbl2 where tem_tbl2.dd_rm_id<a.dd_rm_id) as rec_no,a.* from (select a.dd_rm_id,a.DD_RM_DATE,a.TRANSFORMERSUBSTATIONID as station_name,a.DD_RM_ROUTENAME as line_name,a.DD_RM_RUNSTATE as dd_rm_runstate_morning,b.DD_RM_RUNSTATE as dd_rm_runstate_noon,c.DD_RM_RUNSTATE as dd_rm_runstate_night from dd_running_mode a,dd_running_mode b,dd_running_mode c where a.transformersubstationid=b.transformersubstationid and b.transformersubstationid=c.transformersubstationid and a.dd_rm_routename=b.dd_rm_routename and b.dd_rm_routename=c.dd_rm_routename and a.dd_rm_date=b.dd_rm_date and b.dd_rm_date=c.dd_rm_date and a.dd_rm_classmode='白班' and b.dd_rm_classmode='中班' and c.dd_rm_classmode='夜班') a) b where mod(b.rec_no,2)=0) t2 on t2.rec_no-t1.rec_no=1 order by t1.rec_no
可以实把整条语句写出来,是这样 select * from table where nvl2(value,字段,1)=nvl(value,1) group by value order by valuegoup by value --假如value是空,不受任何影响
使用字符窜构造,使用动态SQL执行。
动态sql
str:='select colname from '||v_tbname||' where rownum=1';
execute immediate str into v_coldata;
具体点就是,我要实现如下函数:
private String addClouse(String strSQL//SQL查询语句,String strWhereClouse//形如"name='小李'"的条件)
{
return "";
}
其中strSQL是一个完整的SQL语句,可能有where条件,order by,group by,嵌套查询,联合查询等。而strWhereClouse是一个形如"name='小李'"的字符串.
如何返回一条合并后合法的SQL语句
使用SUBSTR(strSQL,1,INSRT(strSQL,'WHERE'))||'name=''小李'''
你知道字符串的连接就OK了
strSQL可以是简单的如"select * from tablename"
也可以是:
select * from dd_produce_journal,(select dd_pj_no,
max(decode(forecast_time,'01:00',forecast_burden)) forecast_burden1,
max(decode(forecast_time,'01:00',real_burden))real_burden1,
max(decode(forecast_time,'02:00',forecast_burden))forecast_burden2,
max(decode(forecast_time,'02:00',real_burden))real_burden2,
max(decode(forecast_time,'03:00',forecast_burden))forecast_burden3,
max(decode(forecast_time,'03:00',real_burden))real_burden3,
max(decode(forecast_time,'04:00',forecast_burden)) forecast_burden4,
max(decode(forecast_time,'04:00',real_burden))real_burden4,
max(decode(forecast_time,'05:00',forecast_burden))forecast_burden5,
max(decode(forecast_time,'05:00',real_burden))real_burden5,
max(decode(forecast_time,'06:00',forecast_burden))forecast_burden6,
max(decode(forecast_time,'06:00',real_burden))real_burden6,
max(decode(forecast_time,'07:00',forecast_burden))forecast_burden7,
max(decode(forecast_time,'07:00',real_burden))real_burden7,
max(decode(forecast_time,'08:00',forecast_burden))forecast_burden8,
max(decode(forecast_time,'08:00',real_burden))real_burden8,
max(decode(forecast_time,'09:00',forecast_burden))forecast_burden9,
max(decode(forecast_time,'09:00',real_burden))real_burden9,
max(decode(forecast_time,'10:00',forecast_burden))forecast_burden10,
max(decode(forecast_time,'10:00',real_burden))real_burden10,
max(decode(forecast_time,'11:00',forecast_burden))forecast_burden11,
max(decode(forecast_time,'11:00',real_burden))real_burden11,
max(decode(forecast_time,'12:00',forecast_burden))forecast_burden12,
max(decode(forecast_time,'12:00',real_burden))real_burden12,
max(decode(forecast_time,'13:00',forecast_burden))forecast_burden13,
max(decode(forecast_time,'13:00',real_burden)) real_burden13,
max(decode(forecast_time,'14:00',forecast_burden))forecast_burden14,
max(decode(forecast_time,'14:00',real_burden))real_burden14,
max(decode(forecast_time,'15:00',forecast_burden))forecast_burden15,
max(decode(forecast_time,'15:00',real_burden))real_burden15,
max(decode(forecast_time,'16:00',forecast_burden))forecast_burden16,
max(decode(forecast_time,'16:00',real_burden))real_burden16,
max(decode(forecast_time,'17:00',forecast_burden))forecast_burden17,
max(decode(forecast_time,'17:00',real_burden))real_burden17,
max(decode(forecast_time,'18:00',forecast_burden))forecast_burden18,
max(decode(forecast_time,'18:00',real_burden))real_burden18,
max(decode(forecast_time,'19:00',forecast_burden))forecast_burden19,
max(decode(forecast_time,'19:00',real_burden))real_burden19,
max(decode(forecast_time,'20:00',forecast_burden))forecast_burden20,
max(decode(forecast_time,'20:00',real_burden))real_burden20,
max(decode(forecast_time,'21:00',forecast_burden))forecast_burden21,
max(decode(forecast_time,'21:00',real_burden))real_burden21,
max(decode(forecast_time,'22:00',forecast_burden))forecast_burden22,
max(decode(forecast_time,'22:00',real_burden))real_burden22,
max(decode(forecast_time,'23:00',forecast_burden))forecast_burden23,
max(decode(forecast_time,'23:00',real_burden))real_burden23,
max(decode(forecast_time,'24:00',forecast_burden))forecast_burden24,
max(decode(forecast_time,'24:00',real_burden))real_burden24 from dd_dd_forecast_dayreport group by dd_pj_no)b
where dd_produce_journal.dd_pj_no=b.dd_pj_no
或者:select t1.dd_rm_date,t1.station_name as station_name_l,t1.line_name as line_name_l,t1.dd_rm_runstate_morning as dd_rm_runstate_morning_l,t1.dd_rm_runstate_noon as dd_rm_runstate_noon_l,t1.dd_rm_runstate_night as dd_rm_runstate_night_l,t2.station_name as station_name_r,t2.line_name as line_name_r,t2.dd_rm_runstate_morning as dd_rm_runstate_morning_r,t2.dd_rm_runstate_noon as dd_rm_runstate_noon_r,t2.dd_rm_runstate_night as dd_rm_runstate_night_r from (select * from (select (select count(*)+1 from
(select a.dd_rm_id,a.DD_RM_DATE,a.TRANSFORMERSUBSTATIONID as station_name,a.DD_RM_ROUTENAME as line_name,a.DD_RM_RUNSTATE as dd_rm_runstate_morning,b.DD_RM_RUNSTATE as dd_rm_runstate_noon,c.DD_RM_RUNSTATE as dd_rm_runstate_night from dd_running_mode a,dd_running_mode b,dd_running_mode c where a.transformersubstationid=b.transformersubstationid and b.transformersubstationid=c.transformersubstationid and a.dd_rm_routename=b.dd_rm_routename and b.dd_rm_routename=c.dd_rm_routename and a.dd_rm_date=b.dd_rm_date and b.dd_rm_date=c.dd_rm_date and a.dd_rm_classmode='白班' and b.dd_rm_classmode='中班' and c.dd_rm_classmode='夜班') tem_tbl1 where
tem_tbl1.dd_rm_id<a.dd_rm_id) as rec_no,a.* from
(select a.dd_rm_id,a.DD_RM_DATE,a.TRANSFORMERSUBSTATIONID as station_name,a.DD_RM_ROUTENAME as line_name,a.DD_RM_RUNSTATE as dd_rm_runstate_morning,b.DD_RM_RUNSTATE as dd_rm_runstate_noon,c.DD_RM_RUNSTATE as dd_rm_runstate_night from dd_running_mode a,dd_running_mode b,dd_running_mode c where a.transformersubstationid=b.transformersubstationid and b.transformersubstationid=c.transformersubstationid and a.dd_rm_routename=b.dd_rm_routename and b.dd_rm_routename=c.dd_rm_routename and a.dd_rm_date=b.dd_rm_date and b.dd_rm_date=c.dd_rm_date and a.dd_rm_classmode='白班' and b.dd_rm_classmode='中班' and c.dd_rm_classmode='夜班') a) b where
mod(b.rec_no,2)=1) t1 left outer join (select * from (select (select count(*)+1 from
(select a.dd_rm_id,a.DD_RM_DATE,a.TRANSFORMERSUBSTATIONID as station_name,a.DD_RM_ROUTENAME as line_name,a.DD_RM_RUNSTATE as dd_rm_runstate_morning,b.DD_RM_RUNSTATE as dd_rm_runstate_noon,c.DD_RM_RUNSTATE as dd_rm_runstate_night from dd_running_mode a,dd_running_mode b,dd_running_mode c where a.transformersubstationid=b.transformersubstationid and b.transformersubstationid=c.transformersubstationid and a.dd_rm_routename=b.dd_rm_routename and b.dd_rm_routename=c.dd_rm_routename and a.dd_rm_date=b.dd_rm_date and b.dd_rm_date=c.dd_rm_date and a.dd_rm_classmode='白班' and b.dd_rm_classmode='中班' and c.dd_rm_classmode='夜班') tem_tbl2 where
tem_tbl2.dd_rm_id<a.dd_rm_id) as rec_no,a.* from
(select a.dd_rm_id,a.DD_RM_DATE,a.TRANSFORMERSUBSTATIONID as station_name,a.DD_RM_ROUTENAME as line_name,a.DD_RM_RUNSTATE as dd_rm_runstate_morning,b.DD_RM_RUNSTATE as dd_rm_runstate_noon,c.DD_RM_RUNSTATE as dd_rm_runstate_night from dd_running_mode a,dd_running_mode b,dd_running_mode c where a.transformersubstationid=b.transformersubstationid and b.transformersubstationid=c.transformersubstationid and a.dd_rm_routename=b.dd_rm_routename and b.dd_rm_routename=c.dd_rm_routename and a.dd_rm_date=b.dd_rm_date and b.dd_rm_date=c.dd_rm_date and a.dd_rm_classmode='白班' and b.dd_rm_classmode='中班' and c.dd_rm_classmode='夜班') a) b where
mod(b.rec_no,2)=0) t2 on t2.rec_no-t1.rec_no=1 order by t1.rec_no
select * from table where nvl2(value,字段,1)=nvl(value,1) group by value order by valuegoup by value --假如value是空,不受任何影响