<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_apply和el_comp_info,所以这里不加。但是注意,这个子查询中使用了内联视图feiminying,所以内联视图需要修改。
9. 最后,注意在monthfinancingneedsamount和monthfinancingneedsnum子查询中,使用了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'`。
另外,在修改内联视图时,注意不要破坏原有的括号结构。
最后,我们还需要注意,在monthfinancingneedsamount和monthfinancingneedsnum子查询中,我们除了修改内联视图,还需要在外部对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)` 处理空值,确保结果始终为数字类型
---