oracle视图查多个表,oracle 同时查询多张表 多张视图

oracle同时查询多张表较为简单，用sql和视图都可以完成，但是oracle查询多张视图就不好做了，要是还有分页和检索的功能就更加蛋疼了，今天老夫遇到了这样的问题，接下来就和大伙分享下：

//分页的语句

String fenyeSql_1 = "SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (";

String fenyeSql_2 = " ) A WHERE ROWNUM <= " + end + " ) WHERE RN >= "    + from + " ";

//查询条数的语句

String fenyeCountSql_1 = " select count(*) from ( ";

String fenyeCountSql_2 = " ) ";

String union = " union ";

//第一张视图

String sql_gjy_select = " select to_char(t.xm) xm,to_char(t.xb) xb,t.qslc qslc,t.zzlc ,to_char(t.检查日期) 检查日期,to_char(t.上传时间) 上传时间,t.上传单位,t.路局名,t.jcsbid sclx from v$gwsj_jcsjlx t where 1=1 ";

//第二张视图

String sql_tqi_select = " select t2.xm xm,t2.xb xb,t2.qslc qslc,t2.zzlc,to_char(t2.检查日期,'yyyy-MM-dd') 检查日期,to_char(t2.上传时间,'yyyy-MM-dd') 上传时间,t2.上传单位,t2.路局名,4 sclx from v$tqi_line t2 where 1=1 ";

//第三张视图

String sql_g4d_select = " select t3.xm xm,t3.xb xb,t3.qslc qslc,t3.zzlc,t3.检查日期 检查日期,to_char(t3.上传时间,'yyyy-MM-dd') 上传时间,t3.上传单位,t3.路局名,5 sclx from v$g4d_line t3  where 1=1 ";

//注意三张视图的数据类型要转换一致，而且列的数量要一样哦

//接下来就是蛋疼的凭借sql

// 条件1

if (null != year && null != month && !"".equals(year)

&& !"".equals(month)) {

String selectDate = year + "-";

if (month.toCharArray().length == 1) {

selectDate += "0" + month;

} else if (month.toCharArray().length == 2) {

selectDate += month;

}

sql_gjy_select += " and t.检查日期  like '%" + selectDate + "%'";

sql_tqi_select += " and t2.检查日期  like '%" + selectDate + "%'";

sql_g4d_select += " and t3.检查日期  like '%" + selectDate + "%'";

}

//条件2

if (null != fdwbh && !"".equals(fdwbh)) {

sql_gjy_select += " and t.上传单位='" + fdwbh + "'";

sql_tqi_select += " and t2.上传单位='" + fdwbh + "'";

sql_g4d_select += " and t3.上传单位='" + fdwbh + "'";

}

//

//

中间条件省略若干的

//

//

// 查询数据

String sql_select = fenyeSql_1 + sql_gjy_select + union

+ sql_tqi_select + union + sql_g4d_select + fenyeSql_2;

// 对应总条数

String sql_count = fenyeCountSql_1 + sql_gjy_select + union

+ sql_tqi_select + union + sql_g4d_select + fenyeCountSql_2;

PreparedStatement ps = null;

String permissionJson = null;

//接下来我就不多说了，该怎么干就怎么干吧

寡人qq 545923664 有事多联系，仅限妙龄女子~0.0