EL

JAVA EL表达式详解
EL表达式总是用大括号括起,而且前面有一个美元符($)前缀:${expression}。
表示式中第一个命名变量要么式一个隐式对象,要么是某个作用域(页面作用域、请求作用域、会话作用域或应用作用域)中的一个属性。
点 号操作符允许你使用一个Map键或一个bean性质名来访问值,例如,使用${foo.bar}可以得到bar的值,在此,bar是Map foo的Map键名,或者是bean foo的一个性质。放在点号操作符右边的东西必须遵循Java的标识符命名规则!(换句话说,必须以一个字母、下划线或美元符开头,第一个字符后面可以有 数字,但不能有其他字符)。
点号右边只能放合法的Java标识符。例如,${foo.1}键就不可以。
[]操作符比点号功能更强大,因为利用[]可以访问数组和List,可以把包含命名变量的表达式放在中括号里,而且可以做任意层次的嵌套,只要你受得了。
例如,如果musicList是一个ArrayList,可以用${musicList[0]}或${musicList[“0”]}来访问列表的第一个值。EL并不关心列表索引加不加引号。
如果中括号里的内容没有用引号引起来,容器就会进行计算。如果确实放在引号里,而且不是一个数组或List的索引,容器就会把它看作是性质或键的直接命名。
除 了一个EL隐式对象(PageContext)外,其他EL隐式对象都是Map。从这些隐式对象可以得到任意4个作用域中的属性、请求参数值、首部值、 cookie值和上下文初始化参数。非映射的隐式对象是pageContext,它是PageContext对象的一个引用。
不 要把隐式EL作用域对象(属性的Map)与属性所绑定的对象混为一谈。换句话说,不要把requestScope隐式对象与具体的JSP隐式对象 request混淆。访问请求对象只有一条路,这就是通过pageContext隐式对象来访问(不过,想从请求得到的一些东西通过其他EL隐式对象也可 以得到,包括param/paramValues、header/headerValues和cookie)。
EL允许你调用一个普通Java类中的公共静态方法。函数名不一定与具体的方法名相匹配!例如,${foo:roolIt()}并不意味着包含函数的类中肯定有一个名为roolIt()的方法。
使 用一个TLD将函数名(例如roolIt())映射到一个具体的静态方法。使用元素声明一个函数,包括函数 的(roolIt())、完全限定类以及,其中包括返回类型以及方法名和参数表。
要在JSP中使用函数,必须使用taglib指令声明一个命名空间。在taglib指令中放一个prefix属性,告诉容器你要的函数在哪个TLD里能找到。例如:
基本语法
一、EL简介
1.语法结构
${expression}
2.[]与.运算符
EL 提供.和[]两种运算符来存取数据。
当要存取的属性名称中包含一些特殊字符,如.或?等并非字母或数字的符号,就一定要使用 []。例如:
       ${user.My-Name}应当改为${user["My-Name"] }
如果要动态取值时,就可以用[]来做,而.无法做到动态取值。例如:
       ${sessionScope.user[data]}中data 是一个变量
3.变量
EL存取变量数据的方法很简单,例如:${username}。它的意思是取出某一范围中名称为username的变量。
因为我们并没有指定哪一个范围的username,所以它会依序从Page、Request、Session、Application范围查找。
假如途中找到username,就直接回传,不再继续找下去,但是假如全部的范围都没有找到时,就回传null。
属性范围在EL中的名称
       Page       PageScope
       Request       RequestScope
       Session       SessionScope
       Application     ApplicationScope
      
二、EL隐含对象
1.与范围有关的隐含对象
与范围有关的EL 隐含对象包含以下四个:pageScope、requestScope、sessionScope 和applicationScope;
它们基本上就和JSP的pageContext、request、session和application一样;
在EL中,这四个隐含对象只能用来取得范围属性值,即getAttribute(String name),却不能取得其他相关信息。
例如:我们要取得session中储存一个属性username的值,可以利用下列方法:
session.getAttribute("username") 取得username的值,
在EL中则使用下列方法
${sessionScope.username}
2.与输入有关的隐含对象
与输入有关的隐含对象有两个:param和paramValues,它们是EL中比较特别的隐含对象。
例如我们要取得用户的请求参数时,可以利用下列方法:
request.getParameter(String name)
request.getParameterValues(String name)
在EL中则可以使用param和paramValues两者来取得数据。
${param.name}
${paramValues.name}
3.其他隐含对象
cookie
JSTL并没有提供设定cookie的动作,
例:要取得cookie中有一个设定名称为userCountry的值,可以使用${cookie.userCountry}来取得它。
header和headerValues
header 储存用户浏览器和服务端用来沟通的数据
例:要取得用户浏览器的版本,可以使用${header["User-Agent"]}。
另外在鲜少机会下,有可能同一标头名称拥有不同的值,此时必须改为使用headerValues 来取得这些值。
initParam
initParam取得设定web站点的环境参数(Context)
例:一般的方法String userid = (String)application.getInitParameter("userid");
可以使用 ${initParam.userid}来取得名称为userid
pageContext
pageContext取得其他有关用户要求或页面的详细信息。
${pageContext.request.queryString}       取得请求的参数字符串
${pageContext.request.requestURL}       取得请求的URL,但不包括请求之参数字符串
${pageContext.request.contextPath}       服务的web application 的名称
${pageContext.request.method}           取得HTTP 的方法(GET、POST)
${pageContext.request.protocol}       取得使用的协议(HTTP/1.1、HTTP/1.0)
${pageContext.request.remoteUser}       取得用户名称
${pageContext.request.remoteAddr }       取得用户的IP 地址
${pageContext.session.new}          判断session 是否为新的
${pageContext.session.id}             取得session 的ID
${pageContext.servletContext.serverInfo} 取得主机端的服务信息
三、EL运算符
1.算术运算符有五个:+、-、*或$、/或div、%或mod
2.关系运算符有六个:==或eq、!=或ne、或gt、=或ge
3.逻辑运算符有三个:&&或and、||或or、!或not
4.其它运算符有三个:Empty运算符、条件运算符、()运算符
例:${empty param.name}、${A?B:C}、${A*(B+C)}
四、EL函数(functions)。
语法:ns:function( arg1, arg2, arg3 …. argN)
其中ns为前置名称(prefix),它必须和taglib 指令的前置名称一置
1 EL表达式用${}表示,可用在所有的HTML和JSP标签中 作用是代替JSP页面中复杂的JAVA代码.
       2 EL表达式可操作常量 变量 和隐式对象. 最常用的 隐式对象有${param}和${paramValues}. ${param}表示返回请求参数中单个字符串的值. ${paramValues}表示返回请求参数的一组值.pageScope表示页面范围的变量.requestScope表示请求对象的变量. sessionScope表示会话范围内的变量.applicationScope表示应用范围的变量.
       3 表示是否禁用EL语言,TRUE表示禁止.FALSE表示不禁止.JSP2.0中默认的启用EL语言.
       4 EL语言可显示 逻辑表达式如${true and false}结果是false 关系表达式如${5>6} 结果是false     算术表达式如 ${5+5} 结果是10
       5 EL中的变量搜索范围是:page request session application 点运算符(.)和"[ ]"都是表示获取变量的值.区别是[ ]可以显示非词类的变量
${uplist[0].lzid == zulist.zname?'selected':'' }

我提供给你一段sql,你需要修改一下,要求是所有关于查询el_requirement_applyel_comp_info表的语句均需要增加del_flag = '0'的条件,修改完成后你需要将完整的代码返回给我,完整的sql如下: SELECT gen_random_uuid() AS id, (( SELECT count(1) AS count FROM el_comp_info WHERE ((substr((el_comp_info.unified_code)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y') feiminying WHERE ((el_comp_info.unified_code)::text = (feiminying.uniscid)::text)))) AND ((el_comp_info.del_flag)::text = '0'::text) AND ((el_comp_info.audit_status)::text = 'AUDITED'::text))))::numeric AS registeredenterprisenum, COALESCE(( SELECT count(1) AS count FROM el_granting_credit, el_financial_institution, el_requirement_apply WHERE (((el_requirement_apply.id)::text = (el_granting_credit.apply_id)::text) AND (substr((el_requirement_apply.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y') feiminying WHERE ((el_requirement_apply.unisc_id)::text = (feiminying.uniscid)::text)))) AND ((el_granting_credit.create_by)::text = (el_financial_institution.user_id)::text))), (0)::bigint) AS creditenterprisenum, COALESCE(( SELECT count(DISTINCT el_requirement_info.unisc_id) AS count FROM el_loan_grant, el_requirement_info, el_financial_institution WHERE ((substr((el_requirement_info.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y') feiminying WHERE ((el_requirement_info.unisc_id)::text = (feiminying.uniscid)::text)))) AND ((el_loan_grant.requirement_id)::text = (el_requirement_info.id)::text) AND ((el_financial_institution.user_id)::text = (el_loan_grant.create_by)::text))), (0)::bigint) AS loanenterprisenum, COALESCE(( SELECT count(DISTINCT el_requirement_info.unisc_id) AS count FROM el_loan_grant, el_requirement_info, el_financial_institution WHERE ((substr((el_requirement_info.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y') feiminying WHERE ((el_requirement_info.unisc_id)::text = (feiminying.uniscid)::text)))) AND ((el_loan_grant.requirement_id)::text = (el_requirement_info.id)::text) AND ((el_financial_institution.user_id)::text = (el_loan_grant.create_by)::text))), (0)::bigint) AS loanmicroenterprisenum, COALESCE(( SELECT sum(el_granting_credit.granting_amount) AS sum FROM el_granting_credit, el_financial_institution, el_requirement_apply WHERE (((el_requirement_apply.id)::text = (el_granting_credit.apply_id)::text) AND (substr((el_requirement_apply.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y') feiminying WHERE ((el_requirement_apply.unisc_id)::text = (feiminying.uniscid)::text)))) AND ((el_granting_credit.create_by)::text = (el_financial_institution.user_id)::text))), (0)::numeric) AS creditamount, COALESCE(( SELECT sum(el_loan_grant.grant_amount) AS sum FROM el_loan_grant, el_requirement_apply, el_financial_institution WHERE (((el_requirement_apply.id)::text = (el_loan_grant.apply_id)::text) AND (substr((el_requirement_apply.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y') feiminying WHERE ((el_requirement_apply.unisc_id)::text = (feiminying.uniscid)::text)))) AND ((el_loan_grant.create_by)::text = (el_financial_institution.user_id)::text))), (0)::numeric) AS loanamount, COALESCE(( SELECT sum(el_loan_grant.grant_amount) AS sum FROM el_loan_grant, el_requirement_apply, el_financial_institution WHERE (((el_requirement_apply.id)::text = (el_loan_grant.apply_id)::text) AND (substr((el_requirement_apply.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y') feiminying WHERE ((el_requirement_apply.unisc_id)::text = (feiminying.uniscid)::text)))) AND ((el_loan_grant.create_by)::text = (el_financial_institution.user_id)::text))), (0)::numeric) AS inclusivemicroenterpriseloanamount, COALESCE(( SELECT count(1) AS count FROM el_loan_grant, el_requirement_apply, el_financial_institution WHERE (((el_requirement_apply.id)::text = (el_loan_grant.apply_id)::text) AND (substr((el_requirement_apply.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y') feiminying WHERE ((el_requirement_apply.unisc_id)::text = (feiminying.uniscid)::text)))) AND ((el_loan_grant.create_by)::text = (el_financial_institution.user_id)::text))), (0)::bigint) AS loannum, COALESCE(( SELECT sum(el_loan_grant.grant_amount) AS sum FROM el_loan_grant, el_requirement_info, el_financial_institution WHERE ((substr((el_requirement_info.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y') feiminying WHERE ((el_requirement_info.unisc_id)::text = (feiminying.uniscid)::text)))) AND ((el_loan_grant.requirement_id)::text = (el_requirement_info.id)::text) AND (el_requirement_info.guarantee_mode = '5'::numeric) AND ((el_loan_grant.create_by)::text = (el_financial_institution.user_id)::text))), (0)::numeric) AS creditloanamount, COALESCE(( SELECT count(1) AS count FROM el_loan_grant, el_requirement_info, el_financial_institution WHERE ((substr((el_requirement_info.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y') feiminying WHERE ((el_requirement_info.unisc_id)::text = (feiminying.uniscid)::text)))) AND ((el_loan_grant.requirement_id)::text = (el_requirement_info.id)::text) AND (el_requirement_info.guarantee_mode = '5'::numeric) AND ((el_loan_grant.create_by)::text = (el_financial_institution.user_id)::text))), (0)::bigint) AS creditloannum, COALESCE(( SELECT sum(el_requirement_info.amount) AS sum FROM el_requirement_info, el_requirement_apply, el_financial_institution WHERE ((substr((el_requirement_info.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y') feiminying WHERE ((el_requirement_info.unisc_id)::text = (feiminying.uniscid)::text)))) AND ((el_requirement_info.id)::text = (el_requirement_apply.requirement_id)::text) AND ((el_requirement_apply.institution_id)::text = (el_financial_institution.id)::text))), (0)::numeric) AS financingneedsamount, COALESCE(( SELECT count(1) AS count FROM el_requirement_info, el_requirement_apply, el_financial_institution WHERE ((substr((el_requirement_info.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y') feiminying WHERE ((el_requirement_info.unisc_id)::text = (feiminying.uniscid)::text)))) AND ((el_requirement_info.id)::text = (el_requirement_apply.requirement_id)::text) AND ((el_financial_institution.id)::text = (el_requirement_apply.institution_id)::text))), (0)::bigint) AS financingneedsnum, COALESCE(( SELECT trunc(avg(to_number((el_loan_grant.loan_rate)::text, '0.00'::text)), 2) AS trunc FROM el_loan_grant, el_requirement_apply WHERE (((el_loan_grant.apply_id)::text = (el_requirement_apply.id)::text) AND (substr((el_requirement_apply.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y') feiminying WHERE ((el_requirement_apply.unisc_id)::text = (feiminying.uniscid)::text)))))), (0)::numeric) AS averagelendingrate, COALESCE(( SELECT count(DISTINCT el_requirement_info.unisc_id) AS count FROM el_loan_grant, el_requirement_info, el_financial_institution WHERE ((substr((el_requirement_info.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y') feiminying WHERE ((el_requirement_info.unisc_id)::text = (feiminying.uniscid)::text)))) AND (to_char(el_loan_grant.update_date, 'yyyy-mm'::text) = to_char(now(), 'yyyy-mm'::text)) AND ((el_loan_grant.requirement_id)::text = (el_requirement_info.id)::text) AND ((el_financial_institution.user_id)::text = (el_loan_grant.create_by)::text))), (0)::bigint) AS monthloanenterprisenum, COALESCE(( SELECT count(1) AS count FROM el_user_info rr, el_financial_institution tt WHERE (((rr.id)::text = (tt.user_id)::text) AND ((rr.user_type)::text = 'FI'::text) AND ((tt.parent_id)::text <> '-1'::text))), (0)::bigint) AS settledinfinancialinstitutionnum, COALESCE(( SELECT count(1) AS count FROM el_financial_product, el_financial_institution WHERE ((el_financial_product.create_by)::text = (el_financial_institution.user_id)::text)), (0)::bigint) AS financialproductsnum, COALESCE(( SELECT sum(el_loan_grant.grant_amount) AS sum FROM el_loan_grant, el_financial_institution, el_requirement_apply WHERE (((el_loan_grant.apply_id)::text = (el_requirement_apply.id)::text) AND (substr((el_requirement_apply.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y') feiminying WHERE ((el_requirement_apply.unisc_id)::text = (feiminying.uniscid)::text)))) AND (to_char(el_loan_grant.update_date, 'yyyy-mm'::text) = to_char(now(), 'yyyy-mm'::text)) AND ((el_loan_grant.create_by)::text = (el_financial_institution.user_id)::text))), (0)::numeric) AS monthloanamount, COALESCE(( SELECT count(1) AS count FROM el_loan_grant, el_financial_institution, el_requirement_apply WHERE (((el_loan_grant.apply_id)::text = (el_requirement_apply.id)::text) AND (substr((el_requirement_apply.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y') feiminying WHERE ((el_requirement_apply.unisc_id)::text = (feiminying.uniscid)::text)))) AND (to_char(el_loan_grant.update_date, 'yyyy-mm'::text) = to_char(now(), 'yyyy-mm'::text)) AND ((el_loan_grant.create_by)::text = (el_financial_institution.user_id)::text))), (0)::bigint) AS monthloannum, COALESCE(( SELECT sum(el_loan_grant.grant_amount) AS sum FROM el_loan_grant, el_requirement_info, el_financial_institution WHERE ((substr((el_requirement_info.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y') feiminying WHERE ((el_requirement_info.unisc_id)::text = (feiminying.uniscid)::text)))) AND ((el_loan_grant.requirement_id)::text = (el_requirement_info.id)::text) AND (el_requirement_info.guarantee_mode = '5'::numeric) AND (to_char(el_loan_grant.update_date, 'yyyy-mm'::text) = to_char(now(), 'yyyy-mm'::text)) AND ((el_loan_grant.create_by)::text = (el_financial_institution.user_id)::text))), (0)::numeric) AS monthcreditloanamount, COALESCE(( SELECT count(1) AS count FROM el_loan_grant, el_requirement_info, el_financial_institution WHERE ((substr((el_requirement_info.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y') feiminying WHERE ((el_requirement_info.unisc_id)::text = (feiminying.uniscid)::text)))) AND ((el_loan_grant.requirement_id)::text = (el_requirement_info.id)::text) AND (el_requirement_info.guarantee_mode = '5'::numeric) AND (to_char(el_loan_grant.update_date, 'yyyy-mm'::text) = to_char(now(), 'yyyy-mm'::text)) AND ((el_loan_grant.create_by)::text = (el_financial_institution.user_id)::text))), (0)::bigint) AS monthcreditloannum, COALESCE(( SELECT sum(el_loan_grant.grant_amount) AS sum FROM el_loan_grant, el_requirement_apply, el_financial_institution WHERE (((el_loan_grant.apply_id)::text = (el_requirement_apply.id)::text) AND (substr((el_requirement_apply.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y') feiminying WHERE ((el_requirement_apply.unisc_id)::text = (feiminying.uniscid)::text)))) AND (to_char(el_loan_grant.update_date, 'yyyy-mm'::text) = to_char(now(), 'yyyy-mm'::text)) AND ((el_loan_grant.create_by)::text = (el_financial_institution.user_id)::text))), (0)::numeric) AS monthinclusivemicroenterpriseloanamount, COALESCE(( SELECT sum(el_requirement_info.amount) AS sum FROM el_requirement_info, el_requirement_apply, el_financial_institution WHERE ((substr((el_requirement_apply.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y') feiminying WHERE ((el_requirement_apply.unisc_id)::text = (feiminying.uniscid)::text)))) AND ((el_requirement_info.id)::text = (el_requirement_apply.requirement_id)::text) AND ((el_requirement_apply.institution_id)::text = (el_financial_institution.id)::text) AND (to_char(el_requirement_info.update_date, 'yyyy-mm'::text) = to_char(now(), 'yyyy-mm'::text)))), (0)::numeric) AS monthfinancingneedsamount, COALESCE(( SELECT count(1) AS count FROM el_requirement_info, el_requirement_apply, el_financial_institution WHERE ((substr((el_requirement_apply.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y') feiminying WHERE ((el_requirement_apply.unisc_id)::text = (feiminying.uniscid)::text)))) AND ((el_requirement_info.id)::text = (el_requirement_apply.requirement_id)::text) AND ((el_requirement_apply.institution_id)::text = (el_financial_institution.id)::text) AND (to_char(el_requirement_info.update_date, 'yyyy-mm'::text) = to_char(now(), 'yyyy-mm'::text)))), (0)::bigint) AS monthfinancingneedsnum, '2023-06-20 08:00:00'::text AS statisticsbegintime, now() AS statisticsendtime
最新发布
07-26
<think>我们被要求在所有查询`el_requirement_apply``el_comp_info`表的地方增加条件`del_flag = '0'`。 注意:原始SQL中已经有一些地方有`del_flag`条件,但我们需要确保所有涉及这两个表的地方都加上这个条件(如果还没有的话)。 分析: 1. 第一个子查询(计算registeredenterprisenum)中,已经对el_comp_info有`(el_comp_info.del_flag)::text = '0'::text`条件。 2. 其他子查询中,我们主要关注是否使用了`el_requirement_apply`或`el_comp_info`表,并且没有加上`del_flag='0'`的条件。 观察发现: - 子查询中,我们使用`el_comp_info`表的地方主要是在一个内联视图(feiminying)中,该视图是从`el_comp_info`中选取`private_enterprises = 'Y'`的记录,但这里没有`del_flag`条件。但是,请注意,这个内联视图是在多个地方被使用的,我们需要在这个内联视图中也加上`del_flag='0'`的条件吗? 根据要求,所有关于查询`el_requirement_apply``el_comp_info`表的语句均需要增加`del_flag = '0'`的条件。因此,内联视图(feiminying)中使用了`el_comp_info`表,所以我们也需要在这个视图中加上`del_flag='0'`。 但是,请注意,在原始SQL中,内联视图feiminying的定义是: (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y') feiminying 我们需要将其修改为: (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y' and del_flag = '0') feiminying 另外,在那些直接使用`el_requirement_apply`或`el_comp_info`表的地方,如果还没有`del_flag`条件,我们需要加上。 我们检查每个子查询: 1. 第一个子查询(registeredenterprisenum)中,el_comp_info已经加了del_flag条件,但是内联视图feiminying没有加。所以内联视图需要加。 2. 第二个子查询(creditenterprisenum)中,使用了el_requirement_apply表,但当前没有del_flag条件。所以我们需要在el_requirement_apply的条件中加上`(el_requirement_apply.del_flag)::text = '0'::text`。同时,内联视图feiminying也需要修改。 3. 第三个子查询(loanenterprisenum)中,使用了el_requirement_info表,但要求中并没有提到el_requirement_info表需要加del_flag,所以这里我们不加。但是,内联视图feiminying是从el_comp_info来的,所以需要修改内联视图。 4. 实际上,所有使用到内联视图feiminying的地方,其来源都是el_comp_info,所以我们需要统一修改内联视图的定义,加上del_flag='0'。 5. 另外,在直接使用el_requirement_apply表的地方,都需要加上del_flag='0'的条件。例如: 第二个子查询(creditenterprisenum)中:在WHERE条件中,我们加上`AND (el_requirement_apply.del_flag)::text = '0'::text` 第五个子查询(creditamount)中:同样需要加 第六个子查询(loanamount)中:需要加 第七个子查询(inclusivemicroenterpriseloanamount)中:需要加 第八个子查询(loannum)中:需要加 等等,只要子查询中出现了el_requirement_apply表,我们都需要加上。 6. 注意:在子查询中,有些表可能使用了别名,比如el_requirement_apply可能被别名,但这里没有别名,所以直接加条件。 7. 另外,在计算averagelendingrate的子查询中,使用了el_requirement_apply,也需要加。 8. 在计算monthloanenterprisenum的子查询中,使用了el_requirement_info,但要求只针对el_requirement_applyel_comp_info,所以这里不加。但是注意,这个子查询中使用了内联视图feiminying,所以内联视图需要修改。 9. 最后,注意在monthfinancingneedsamountmonthfinancingneedsnum子查询中,使用了el_requirement_apply,所以需要加del_flag条件。 因此,我们需要做两件事: (1) 修改所有内联视图feiminying的定义,在where条件中增加`and del_flag='0'`。 (2) 在所有直接使用el_requirement_apply表的地方,增加条件`AND (el_requirement_apply.del_flag)::text = '0'::text`(注意保持原有的类型转换)。 但是,注意原始SQL中有些地方已经对el_comp_info表加了del_flag条件(如第一个子查询),而内联视图feiminying是独立的,所以我们需要修改内联视图。 修改步骤: 我们将内联视图: (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y') feiminying 修改为: (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y' and del_flag='0') feiminying 然后,在每一个使用到el_requirement_apply表的子查询中,在WHERE条件中加入对el_requirement_apply的del_flag条件。 注意:有些子查询中已经存在el_requirement_apply的条件,我们只需要追加即可。 由于SQL很长,我们逐个子查询修改。 但是,请注意,有些子查询中并没有直接使用el_requirement_apply,而是使用了el_requirement_info,所以不需要加。我们只针对el_requirement_apply表。 我们列出需要修改的地方(即包含el_requirement_apply表的子查询): 1. creditenterprisenum: FROM el_granting_credit, el_financial_institution, el_requirement_apply 在WHERE条件中,加上: AND (el_requirement_apply.del_flag)::text = '0'::text 2. creditamount: 同creditenterprisenum,同样加上。 3. loanamount: FROM el_loan_grant, el_requirement_apply, el_financial_institution 在WHERE条件中,加上: AND (el_requirement_apply.del_flag)::text = '0'::text 4. inclusivemicroenterpriseloanamount: 同loanamount,加上。 5. loannum: 同loanamount,加上。 6. averagelendingrate: FROM el_loan_grant, el_requirement_apply 在WHERE条件中,加上: AND (el_requirement_apply.del_flag)::text = '0'::text 7. monthloanamount: FROM el_loan_grant, el_financial_institution, el_requirement_apply 加上: AND (el_requirement_apply.del_flag)::text = '0'::text 8. monthloannum: 同monthloanamount,加上。 9. monthinclusivemicroenterpriseloanamount: 同monthloanamount,加上。 10. monthfinancingneedsamount: FROM el_requirement_info, el_requirement_apply, el_financial_institution 注意:这里使用了el_requirement_apply,所以需要加: AND (el_requirement_apply.del_flag)::text = '0'::text 11. monthfinancingneedsnum: 同monthfinancingneedsamount,加上。 另外,在第一个子查询(registeredenterprisenum)中,内联视图feiminying需要修改,但该子查询本身已经对el_comp_info加了del_flag,不过内联视图是独立的,所以内联视图也要单独加。 同时,其他所有使用到feiminying的地方(即所有有内联视图feiminying的地方)都需要修改内联视图的定义。 由于内联视图feiminying在多个子查询中出现,我们需要在每一个出现的地方都修改。 因此,我们遍历整个SQL,将每一个内联视图feiminying的定义都加上`and del_flag='0'`。 注意:内联视图feiminying在以下子查询中出现(通过搜索`feiminying`): - registeredenterprisenum - creditenterprisenum - loanenterprisenum - loanmicroenterprisenum (与loanenterprisenum相同,但独立出现) - creditamount - loanamount - inclusivemicroenterpriseloanamount - creditloanamount - creditloannum - financingneedsamount - financingneedsnum - averagelendingrate - monthloanenterprisenum - monthloanamount - monthloannum - monthcreditloanamount - monthcreditloannum - monthinclusivemicroenterpriseloanamount - monthfinancingneedsamount - monthfinancingneedsnum 所以,我们需要修改所有上述子查询中的内联视图定义。 修改内联视图的语句: 将: (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y') feiminying 改为: (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y' and del_flag='0') feiminying 注意:在修改内联视图时,我们增加了条件`and del_flag='0'`,这里没有类型转换,因为原表del_flag可能是字符型,而'0'也是字符。但原SQL中其他地方使用`(el_comp_info.del_flag)::text = '0'::text`,为了保持一致,我们可以写成`and del_flag='0'`,因为原表定义可能是字符型。 但是,为了确保安全,我们查看原SQL中第一个子查询对del_flag的处理:`(el_comp_info.del_flag)::text = '0'::text`。在内联视图中,我们也可以这样写,但为了简洁,且考虑到内联视图的where条件中已经有一个条件(private_enterprises='Y'),我们直接加`and del_flag='0'`。 另外,在修改内联视图时,注意不要破坏原有的括号结构。 最后,我们还需要注意,在monthfinancingneedsamountmonthfinancingneedsnum子查询中,我们除了修改内联视图,还需要在外部对el_requirement_apply加del_flag条件(上面已经提到)。 由于修改点很多,我们逐处修改。 但是,为了避免遗漏,我们可以用文本替换的方式,将所有的内联视图定义替换掉。因为内联视图的定义在每一处都是一样的(除了换行空格)。 观察原始SQL中的内联视图写法,都是: (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y') feiminying 我们将其替换为: (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y' and del_flag='0') feiminying 注意:原始SQL中,这个内联视图的写法可能有空格差异,但基本一致。我们可以用这个字符串匹配。 但是,为了确保准确,我们也可以一个一个修改。 由于SQL文本很长,我们在这里直接修改整个SQL字符串。 我们按照上述要求修改。 注意:在修改SQL时,我们还要注意,有些地方可能已经存在del_flag条件(如第一个子查询中的el_comp_info表),但内联视图是独立的,所以内联视图的修改不受外部影响。 开始修改: 步骤: 1. 替换所有内联视图feiminying的定义,加上`and del_flag='0'`。 2. 在直接使用el_requirement_apply表的子查询中,添加el_requirement_apply.del_flag='0'的条件。 由于原始SQL很长,我们将在修改后的完整SQL中体现。 下面给出修改后的完整SQL。</think>根据您的要求,我已修改原始SQL,确保所有查询`el_requirement_apply``el_comp_info`表的地方都增加了`del_flag = '0'`的条件。主要修改如下: 1. **内联视图 `feiminying` 修改**: 在所有子查询的内联视图 `feiminying` 中,为 `el_comp_info` 表添加 `del_flag = '0'` ```sql (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y' and del_flag = '0') feiminying ``` 2. **`el_requirement_apply` 表条件添加**: 在所有直接使用 `el_requirement_apply` 表的子查询中,添加 `(el_requirement_apply.del_flag)::text = '0'::text` 以下是完整的修改后SQL代码: ```sql SELECT gen_random_uuid() AS id, (( SELECT count(1) AS count FROM el_comp_info WHERE ((substr((el_comp_info.unified_code)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y' and del_flag = '0') feiminying WHERE ((el_comp_info.unified_code)::text = (feiminying.uniscid)::text)))) AND ((el_comp_info.del_flag)::text = '0'::text) AND ((el_comp_info.audit_status)::text = 'AUDITED'::text))))::numeric AS registeredenterprisenum, COALESCE(( SELECT count(1) AS count FROM el_granting_credit, el_financial_institution, el_requirement_apply WHERE (((el_requirement_apply.id)::text = (el_granting_credit.apply_id)::text) AND (substr((el_requirement_apply.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y' and del_flag = '0') feiminying WHERE ((el_requirement_apply.unisc_id)::text = (feiminying.uniscid)::text)))) AND ((el_granting_credit.create_by)::text = (el_financial_institution.user_id)::text) AND ((el_requirement_apply.del_flag)::text = '0'::text))), (0)::bigint) AS creditenterprisenum, COALESCE(( SELECT count(DISTINCT el_requirement_info.unisc_id) AS count FROM el_loan_grant, el_requirement_info, el_financial_institution WHERE ((substr((el_requirement_info.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y' and del_flag = '0') feiminying WHERE ((el_requirement_info.unisc_id)::text = (feiminying.uniscid)::text)))) AND ((el_loan_grant.requirement_id)::text = (el_requirement_info.id)::text) AND ((el_financial_institution.user_id)::text = (el_loan_grant.create_by)::text))), (0)::bigint) AS loanenterprisenum, COALESCE(( SELECT count(DISTINCT el_requirement_info.unisc_id) AS count FROM el_loan_grant, el_requirement_info, el_financial_institution WHERE ((substr((el_requirement_info.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y' and del_flag = '0') feiminying WHERE ((el_requirement_info.unisc_id)::text = (feiminying.uniscid)::text)))) AND ((el_loan_grant.requirement_id)::text = (el_requirement_info.id)::text) AND ((el_financial_institution.user_id)::text = (el_loan_grant.create_by)::text))), (0)::bigint) AS loanmicroenterprisenum, COALESCE(( SELECT sum(el_granting_credit.granting_amount) AS sum FROM el_granting_credit, el_financial_institution, el_requirement_apply WHERE (((el_requirement_apply.id)::text = (el_granting_credit.apply_id)::text) AND (substr((el_requirement_apply.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 极 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y' and del_flag = '0') feiminying WHERE ((el_requirement_apply.unisc_id)::text = (feiminying.uniscid)::text)))) AND ((el_granting_credit.create_by)::text = (el_financial_institution.user_id)::text) AND ((el_requirement_apply.d极lag)::text = '0'::text))), (0)::numeric) AS creditamount, COALESCE(( SELECT sum(el_loan_grant.grant_amount) AS sum FROM el_loan_grant, el_requirement_apply, el_financial_institution WHERE (((el_requirement_apply.id)::text = (el_loan_grant.apply_id)::text) AND (substr((el_requirement_apply.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y' and del_flag = '0') feiminying WHERE ((el_requirement_apply.unisc_id)::text = (feiminying.uniscid)::text)))) AND ((el_loan_grant.create_by)::text = (el_financial_institution.user_id)::text) AND ((el_requirement_apply.del_flag)::text = '0'::text))), (0)::numeric) AS loanamount, COALESCE(( SELECT sum(el_loan_grant.grant_amount) AS sum FROM el_loan_grant, el_requirement_apply, el_financial_institution WHERE (((el_requirement_apply.id)::text = (el_loan_grant.apply_id)::text) AND (substr((el_requirement_apply.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y' and del_flag = '0') feiminying WHERE ((el_requirement_apply.unisc_id)::text = (feiminying.uniscid)::text)))) AND ((el_loan_grant.create_by)::text = (el_financial_institution.user_id)::text) AND ((el_requirement_apply.del_flag)::text = '0'::text))), (0)::numeric) AS inclusivemicroenterpriseloanamount, COALESCE(( SELECT count(1) AS count FROM el_loan_grant, el_requirement_apply, el_financial_institution WHERE (((el_requirement_apply.id)::text = (el极_loan_grant.apply_id)::text) AND (substr((el_requirement_apply.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y' and del_flag = '0') feiminying WHERE ((el_requirement_apply.unisc_id)::text = (feiminying.uniscid)::text)))) AND ((el_loan_grant.create_by)::text = (el_financial_institution.user_id)::text) AND ((el_requirement_apply.del_flag)::text = '0'::text))), (0)::bigint) AS loannum, COALESCE(( SELECT sum(el_loan_grant.grant_amount) AS sum FROM el_loan_grant, el_requirement_info, el_financial_institution WHERE ((substr((el_requirement_info.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y' and del_flag = '0') feiminying WHERE ((el_requirement_info.unisc_id)::text = (feiminying.uniscid)::text)))) AND ((el_loan_grant.requirement_id)::text = (el_requirement_info.id)::text) AND (el_requirement_info.guarantee_mode = '5'::numeric) AND ((el_loan_grant.create_by)::text = (el_financial_institution.user_id)::text))), (0)::numeric) AS creditloanamount, COALESCE(( SELECT count(1) AS count FROM el_loan_grant, el_requirement_info, el_financial_institution WHERE ((substr((el_requirement_info.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y' and del_flag = '0') feiminying WHERE ((el_requirement_info.unisc_id)::text = (feiminying.uniscid)::text)))) AND ((el_loan_grant.requirement_id)::text = (el_requirement_info.id)::text) AND (el_requirement_info.guarantee_mode = '5'::numeric) AND ((el_loan_grant.create_by)::text = (el_financial_institution.user_id)::text))), (0)::bigint) AS creditloannum, COALESCE(( SELECT sum(el_requirement_info.amount) AS sum FROM el_requirement_info, el_requirement_apply, el_financial_institution WHERE ((substr((el_requirement_info.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y' and del_flag = '0') feiminying WHERE ((el_requirement_info.unisc_id)::text = (feiminying.uniscid)::text)))) AND ((el_requirement_info.id)::text = (el_requirement_apply.requirement_id)::text) AND ((el_requirement_apply.institution_id)::text = (el_financial_institution.id)::text) AND ((el_requirement_apply.del_flag)::text = '0'::text))), (0)::numeric) AS financingneedsamount, COALESCE(( SELECT count(1) AS count FROM el_requirement_info, el_requirement_apply, el_financial_institution WHERE ((substr((el_requirement_info.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y' and del_flag = '0') feiminying WHERE ((el_requirement_info.unisc_id)::text = (feiminying.uniscid)::text)))) AND ((el_requirement_info.id)::text = (el_requirement_apply.requirement_id)::text) AND ((el_financial_institution.id)::text = (el_requirement_apply.institution_id)::text) AND ((el_requirement_apply.del_flag)::text = '0'::text))), (0)::bigint) AS financingneedsnum, COALESCE(( SELECT trunc(avg(to_number((el_loan_grant.loan_rate)::text, '0.00'::text)), 2) AS trunc FROM el_loan_grant, el_requirement_apply WHERE (((el_loan_grant.apply_id)::text = (el_requirement_apply.id)::text) AND (substr((el_requirement_apply.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y' and del_flag = '0') feiminying WHERE ((el_requirement_apply.unisc_id)::text = (feiminying.uniscid)::text)))) AND ((el_requirement_apply.del_flag)::text = '0'::text))), (0)::numeric) AS averagelendingrate, COALESCE(( SELECT count(DISTINCT el_requirement_info.unisc_id) AS count FROM el_loan_grant, el_requirement_info, el_financial_institution WHERE ((substr((el_requirement_info.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y' and del_flag = '0') feiminying WHERE ((el_requirement_info.unisc_id)::text = (feiminying.uniscid)::text)))) AND (to_char(el_loan_grant.update_date, 'yyyy-mm'::text) = to_char(now(), 'yyyy-mm'::text)) AND ((el_loan_grant.requirement_id)::text = (el_requirement_info.id)::text) AND ((el_financial_institution.user_id)::text = (el_loan_grant.create_by)::text))), (0)::bigint) AS monthloanenterprisenum, COALESCE(( SELECT count(1) AS count FROM el_user_info rr, el_financial_institution tt WHERE (((rr.id)::text = (tt.user_id)::text) AND ((rr.user_type)::text = 'FI'::text) AND ((tt.parent_id)::text <> '-1'::text))), (0)::bigint) AS settledinfinancialinstitutionnum, COALESCE(( SELECT count(1) AS count FROM el_financial_product, el_financial_institution WHERE ((el_financial_product.create_by)::text = (el_financial_institution.user_id)::text)), (0)::bigint) AS financialproductsnum, COALESCE(( SELECT sum(el_loan_grant.grant_amount) AS sum FROM el_loan_grant, el_financial_institution, el_requirement_apply WHERE (((el_loan_grant.apply_id)::text = (el_requirement_apply.id)::text) AND (substr((el_requirement_apply.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y' and del_flag = '0') feiminying WHERE ((el_requirement_apply.unisc_id)::text = (feiminying.uniscid)::text)))) AND (to_char(el_loan_grant.update_date, 'yyyy-mm'::text) = to_char(now(), 'yyyy-mm'::text)) AND ((el_loan_grant.create_by)::text = (el_financial_institution.user_id)::text) AND ((el_requirement_apply.del_flag)::text = '0'::text))), (0)::numeric) AS monthloanamount, COALESCE(( SELECT count(1) AS count FROM el_loan_grant, el_financial_institution, el_requirement_apply WHERE (((el_loan_grant.apply_id)::text = (el_requirement_apply.id)::text) AND (substr((el_requirement_apply.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y' and del_flag = '0') feiminying WHERE ((el_requirement_apply.unisc_id)::text = (feiminying.uniscid)::text)))) AND (to_char(el_loan_grant.update_date, 'yyyy-mm'::text) = to_char(now(), 'yyyy-mm'::text)) AND ((el_loan_grant.create_by)::text = (el_financial_institution.user_id)::text) AND ((el_requirement_apply.del_flag)::text = '0'::text))), (0)::big极int) AS monthloannum, COALESCE(( SELECT sum(el_loan_grant.grant_amount) AS sum FROM el_loan_grant, el_requirement_info, el_financial_institution WHERE ((substr((el_requirement_info.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y' and del_flag = '0') feiminying WHERE ((el_requirement_info.unisc_id)::text = (feiminying.uniscid)::text)))) AND ((el_loan_grant.requirement_id)::text = (el_requirement_info.id)::text) AND (el_requirement_info.guarantee_mode = '5'::numeric) AND (to_char(el_loan_grant.update_date, 'yyyy-mm'::text) = to_char(now(), 'yyyy-mm'::text)) AND ((el_loan_grant.create_by)::text = (el_financial_institution.user_id)::text))), (0)::numeric) AS monthcreditloanamount, COALESCE(( SELECT count(1) AS count FROM el_loan_grant, el_requirement_info, el_financial_institution WHERE ((substr((el_requirement_info.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y' and del_flag = '0') feiminying WHERE ((el_requirement_info.unisc_id)::text = (feiminying.uniscid)::text)))) AND ((el_loan_grant.requirement_id)::text = (el_requirement_info.id)::text) AND (el_requirement_info.guarantee_mode = '5'::numeric) AND (to_char(el_loan_grant.update_date, 'yyyy-mm'::text) = to_char(now(), 'yyyy-mm'::text)) AND ((el_loan_grant.create_by)::text = (el_financial_institution.user_id)::text))), (0)::bigint) AS monthcreditloannum, COALESCE(( SELECT sum(el_loan_grant.grant_amount) AS sum FROM el_loan_grant, el_requirement_apply, el_financial_institution WHERE (((el_loan_grant.apply_id)::text = (el_requirement_apply.id)::text) AND (substr((el_requirement_apply.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y' and del_flag = '0') feiminying WHERE ((el_requirement_apply.unisc_id)::text = (feiminying.uniscid)::text)))) AND (to_char(el_loan_grant.update_date, 'yyyy-mm'::text) = to_char(now(), 'yyyy-mm'::text)) AND ((el_loan_grant.create_by)::text = (el_financial_institution.user_id)::text) AND ((el_requirement_apply.del_flag)::text = '0'::text))), (0)::numeric) AS monthinclusivemicroenterpriseloanamount, COALESCE(( SELECT sum(el_requirement_info.amount) AS sum FROM el_requirement_info, el_requirement_apply, el_financial_institution WHERE ((substr((el_requirement_apply.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y' and del_flag = '0') feiminying WHERE ((el_requirement_apply.unisc_id)::text = (feiminying.uniscid)::text)))) AND ((el_requirement_info.id)::text = (el_requirement_apply.requirement_id)::text) AND ((el_requirement_apply.institution_id)::text = (el_financial_institution.id)::text) AND (to_char(el_requirement_info.update_date, 'yyyy-mm'::text) = to_char(now(), 'yyyy-mm'::text)) AND ((el_requirement_apply.del_flag)::text = '0'::text))), (0)::numeric) AS monthfinancingneedsamount, COALESCE(( SELECT count(1) AS count FROM el_requirement_info, el_requirement_apply, el_financial_institution WHERE ((substr((el_requirement_apply.unisc_id)::text, 0, 3) = '91'::text) AND ( (EXISTS ( SELECT 1 from (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y' and del_flag = '0') feiminying WHERE ((el_requirement_apply.unisc_id)::text = (feiminying.uniscid)::text)))) AND ((el_requirement_info.id)::text = (el_requirement_apply.requirement_id)::text) AND ((el_requirement_apply.institution_id)::text = (el_financial_institution.id)::text) AND (to_char(el_requirement_info.update_date, 'yyyy-mm'::text) = to_char(now(), 'yyyy-mm'::text)) AND ((el_requirement_apply.del_flag)::text = '0'::text))), (0)::bigint) AS monthfinancingneedsnum, '2023-06-20 08:00:00'::text AS statisticsbegintime, now() AS statisticsendtime ``` ### 修改说明: 1. **内联视图 `feiminying` 的修改**: - 在所有子查询中,将内联视图的定义从: ```sql (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y') feiminying ``` 修改为: ```sql (select unified_code as uniscid from el_comp_info where private_enterprises = 'Y' and del_flag = '0') feiminying ``` - 确保 `el_comp_info` 表的软删除过滤条件生效 2. **`el_requirement_apply` 表条件添加**: - 在所有直接使用 `el_requirement_apply` 的子查询(如 `creditenterprisenum`, `creditamount`, `loanamount` 等)的 `WHERE` 子句中添加: ```sql AND ((el_requirement_apply.del_flag)::text = '0'::text) ``` - 涉及约 10 个子查询(具体位置见代码中的标记) ### 注意事项: - 该 SQL 使用了 PostgreSQL 特有的 `gen_random_uuid()` 函数,确保数据库已启用 `pgcrypto` 扩展 - 所有时间过滤条件使用 `to_char(..., 'yyyy-mm')` 实现按月统计 - 使用 `COALESCE(..., 0)` 处理空值,确保结果始终为数字类型 ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值