最近遇到一个需求需要查出3个表的数据,并且有两个表不能连表查询,他们有各自的筛选条件,写出的sql语句如下:
select 'c' || ec.base_dep_code || '_' || ec.no id,
ec.base_dep_code || '_' || ec.no username,
ec.name,
'c' usertype,
bd.input_code base_dep_name,
ec.no
from ERP_CUSTOMER ec
left join base_dep bd
on bd.code = ec.base_dep_code
where 1 = 1
and Upper(ec.name || ec.base_dep_code || ec.no ||
ec.input_code) like '%CP%'
union all
select ee.id ID,
ee.name as username,
ee.name as Name,
ee.Input_Code usertype,
bd.short_name base_dep_name,
ee.id as NO
from ERP_EMPLOYEE ee
left join base_dep bd
on bd.code = ee.base_dep_code
where 1 = 1
and Upper(ee.name || ee.input_code || ee.email) like "cp"
'%CP%'
这样写很不灵活,遇到筛选条件一变就无法继续使用了,
现在我们可以这样做,写一个视图,把这3个表所有的数据用一个视图查出来,放在一个虚拟的表中,然后再在这个虚拟的表上加筛选条件即可,这了样让语句变的灵活,且达到视图的复用,并且更易阅读.
create or replace view v_system_user as
select
'c' || e.base_dep_code||'_'||e.no as id,
e.base_dep_code||'_'||e.no as username,
e.name,
'' password,
'c' as type,
'客户' as type_name,
input_code,
e.sex gender,
(select name from base_dep bd where bd.code = e.base_dep_code) base_dep_name,
e.base_dep_code,
'' state,
telephone,
e.email,
mobile,
qq,
'' receive_sms,
'' receive_sms_start_time,
'' receive_sms_end_time,
'' question,
'' answer
from erp_customer e
union all
select
'w' || ee.id as id,
ee.id username,
ee.name,
'' password,
'w' as type,
'员工' as type_name,
input_code,
ee.sex gender,
(select name from base_dep bd where bd.code = ee.base_dep_code) base_dep_name,
ee.base_dep_code,
'' state,
telephone,
ee.email,
mobile,
qq,
'' receive_sms,
'' receive_sms_start_time,
'' receive_sms_end_time,
'' question,
'' answer
from erp_employee ee;
这个视图查出来的数据是包括这几个表联合后的所有信息,这样如果在这个试图基础上去加筛选条件就会使得查询数据变得十分的简单.
select
'c' || e.base_dep_code||'_'||e.no as id,
e.base_dep_code||'_'||e.no as username,
e.name,
'' password,
'c' as type,
'客户' as type_name,
input_code,
e.sex gender,
(select name from base_dep bd where bd.code = e.base_dep_code) base_dep_name,
e.base_dep_code,
'' state,
telephone,
e.email,
mobile,
qq,
'' receive_sms,
'' receive_sms_start_time,
'' receive_sms_end_time,
'' question,
'' answer
from erp_customer e
union all
select
'w' || ee.id as id,
ee.id username,
ee.name,
'' password,
'w' as type,
'员工' as type_name,
input_code,
ee.sex gender,
(select name from base_dep bd where bd.code = ee.base_dep_code) base_dep_name,
ee.base_dep_code,
'' state,
telephone,
ee.email,
mobile,
qq,
'' receive_sms,
'' receive_sms_start_time,
'' receive_sms_end_time,
'' question,
'' answer
from erp_employee ee;
这个视图查出来的数据是包括这几个表联合后的所有信息,这样如果在这个试图基础上去加筛选条件就会使得查询数据变得十分的简单.
在写代码的时候就可以把数据库变成试图这样代码更加清晰且容易阅读,并且也十分规范.
private static final String TABLENAME = "V_SYSTEM_USER";申明视图
StringBuffer sql = new StringBuffer();
sql.append(" select * from " + TABLENAME + " t where 1=1 " );
if(!CommonFun.isNe(filter)){
sql.append(" and Upper(id||username||name||type_name||base_dep_code||email||input_code) like :filter");
}
params.put("filter", "%" + filter.toUpperCase() + "%");
sql.append(" select * from " + TABLENAME + " t where 1=1 " );
if(!CommonFun.isNe(filter)){
sql.append(" and Upper(id||username||name||type_name||base_dep_code||email||input_code) like :filter");
}
params.put("filter", "%" + filter.toUpperCase() + "%");