Question

如何用oracle数据库完成4张表的查询啊？？求大神告知

Answer 1

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;