case when orinfo.apply_no in (SELECT DISTINCT apply_no FROM csrop_case.case_approve_record WHERE dispose_node_name IN ('信审初审','组长复审','部门经理审核','部门总监审核','总经理审核','信审专员审核','信审助理审核','初审(0-15]','初审(15-30]','初审(30-60]','初审60以上') AND approve_remark IS NOT NULL and del_flag = '0') then '是' else '否' end 是否人工信审,
时间: 2023-09-08 21:12:31 浏览: 48
这段 SQL 查询语句使用了 `CASE WHEN` 表达式和子查询。其中,`ORINFO` 是主查询的表名,`APPLY_NO` 是表中的一个列名。
该语句中的 `CASE WHEN` 表达式用于判断 `ORINFO` 表中的 `APPLY_NO` 是否存在于子查询的结果集中。如果存在,则返回 `'是'`,否则返回 `'否'`。
子查询会从 `csrop_case.case_approve_record` 表中获取 `DISTINCT` 去重后的 `APPLY_NO` 列表,该列表中的每个值必须同时满足以下条件:
1. `DISPOSE_NODE_NAME` 在指定值列表中;
2. `APPROVE_REMARK` 不为 `NULL`;
3. `DEL_FLAG` 等于 `'0'`。
如果 `ORINFO` 表中的 `APPLY_NO` 在子查询的结果集中出现,则返回 `'是'`,否则返回 `'否'`。
相关问题
优化以下SQL select pao.id, pao.order_no, pao.apply_time, pao.purchase_user_id, pao.purchase_user_name, pao.apply_user_id, pao.apply_user_name, pao.apply_department_id, pao.apply_department_name, pao.apply_end_time, pao.create_user_id, pao.create_user_name, pao.approve_type, pao.approve_user_id, pao.approve_user_name, pao.approve_time, pao.description, pao.order_type, pao.purchase_type, pao.storage_type, pao.compose_order_no, pao.company_id, pao.delete, pao.create_time, pao.update_time, pao.supplier_id, pao.image_path, pao.contract_id, pao.status, pao.invoice_signer_name, pao.total_amount, pao.total_amount_tax, pao.purchase_status, pao.cancel_reason, pao.print_status, pao.demand_id, pao.arrival_status, pao.supervise_num, pao.supervise_date, pao.merge_apply_id, pao.deadline, pao.remind , s.name as supplierName, paod.amount, cm.return_status as returnStatus, cm.inventory_status as inventoryStatus, cm.stock_remark, cm.merge_flag, cm.signature_file, cm.department_pass, cm.receipt_file, cm.amount_paid, cm.amount_unpaid, cm.contract_name, cm.status as contractStatus, cm.contract_no, cm.contract_amount, paod.product_name, cm.advance_payment, cm.advance_ratio, cm.currency_unit from purchase_apply_order pao left join supplier s on pao.supplier_id = s.id left join ( SELECT GROUP_CONCAT(distinct p.product_name) product_name, sum(IFNULL(amount_tax, 0)) amount, apply_order_no from purchase_apply_order_details pa left join product p on p.pn_code = pa.product_code where p.company_id = 29 GROUP BY apply_order_no ) paod on paod.apply_order_no = pao.order_no left join contract_management cm on pao.contract_id = cm.id where pao.delete = 0 and pao.company_id = 29 and deadline <= '2023-05-25 15:34:00.01' and remind = 0 and arrival_status in( 0 , 1 ) order by pao.create_time desc;
首先,我们可以对该 SQL 查询语句进行简化,只选择需要的字段而不是全部选择。其次,我们可以尝试添加索引以加快查询速度。
以下是优化后的 SQL 查询语句:
```
SELECT pao.id, pao.order_no, pao.apply_time, pao.purchase_user_id, pao.purchase_user_name, pao.apply_user_id,
pao.apply_user_name, pao.apply_department_id, pao.apply_department_name, pao.apply_end_time, pao.create_user_id,
pao.create_user_name, pao.approve_type, pao.approve_user_id, pao.approve_user_name, pao.approve_time,
pao.description, pao.order_type, pao.purchase_type, pao.storage_type, pao.compose_order_no, pao.company_id,
pao.delete, pao.create_time, pao.update_time, pao.supplier_id, pao.image_path, pao.contract_id, pao.status,
pao.invoice_signer_name, pao.total_amount, pao.total_amount_tax, pao.purchase_status, pao.cancel_reason,
pao.print_status, pao.demand_id, pao.arrival_status, pao.supervise_num, pao.supervise_date, pao.merge_apply_id,
pao.deadline, pao.remind, s.name AS supplierName, paod.amount, cm.return_status AS returnStatus,
cm.inventory_status AS inventoryStatus, cm.stock_remark, cm.merge_flag, cm.signature_file, cm.department_pass,
cm.receipt_file, cm.amount_paid, cm.amount_unpaid, cm.contract_name, cm.status AS contractStatus,
cm.contract_no, cm.contract_amount, paod.product_name, cm.advance_payment, cm.advance_ratio, cm.currency_unit
FROM purchase_apply_order pao
LEFT JOIN supplier s ON pao.supplier_id = s.id
LEFT JOIN (
SELECT GROUP_CONCAT(DISTINCT p.product_name) product_name, SUM(IFNULL(amount_tax, 0)) amount, apply_order_no
FROM purchase_apply_order_details pa
LEFT JOIN product p ON p.pn_code = pa.product_code
WHERE p.company_id = 29
GROUP BY apply_order_no
) paod ON paod.apply_order_no = pao.order_no
LEFT JOIN contract_management cm ON pao.contract_id = cm.id
WHERE pao.delete = 0
AND pao.company_id = 29
AND deadline <= '2023-05-25 15:34:00.01'
AND remind = 0
AND arrival_status IN (0, 1)
ORDER BY pao.create_time DESC;
```
我们可以为 `purchase_apply_order` 表的 `company_id` 字段和 `deadline` 字段以及 `remind` 字段创建索引。同时,也可以为 `arrival_status` 字段和 `delete` 字段创建索引。这可以大大加快查询速度。
```
CREATE INDEX idx_purchase_apply_order_company_id_deadline_remind ON purchase_apply_order (company_id, deadline, remind);
CREATE INDEX idx_purchase_apply_order_arrival_status_delete ON purchase_apply_order (arrival_status, delete);
```
select t1.month_id, count(distinct case when t1.offer_type_name='主套餐' then offer_name end) aa, count(distinct case when t1.offer_type_name='促销' then offer_name end) bb, count(distinct case when t1.offer_type_name='可选包' then offer_name end) cc, count(distinct case when t2.offer_type_name='主套餐' then offer_name end) aaa, count(distinct case when t2.offer_type_name='促销' then offer_name end) bbb, count(distinct case when t2.offer_type_name='可选包' then offer_name end) ccc, from zone_crmchk.dwd_sum_zhuliuxxsc t1 left join zone_crmchk.dwd_sum_zhuliuxxsc t2 on t2.month < t1.month group by t1.month, count(distinct case when offer_type_name='主套餐' then offer_name end) aa, count(distinct case when offer_type_name='促销' then offer_name end) bb, count(distinct case when offer_type_name='可选包' then offer_name end) cc
根据您提供的 SQL 查询语句,我发现其中存在一个逗号(,)出现在了 FROM 子句之后,这是一个语法错误。您需要将逗号(,)删除,同时将查询语句修改为如下形式:
```
SELECT
t1.month_id,
COUNT(DISTINCT CASE WHEN t1.offer_type_name='主套餐' THEN offer_name END) aa,
COUNT(DISTINCT CASE WHEN t1.offer_type_name='促销' THEN offer_name END) bb,
COUNT(DISTINCT CASE WHEN t1.offer_type_name='可选包' THEN offer_name END) cc,
COUNT(DISTINCT CASE WHEN t2.offer_type_name='主套餐' THEN offer_name END) aaa,
COUNT(DISTINCT CASE WHEN t2.offer_type_name='促销' THEN offer_name END) bbb,
COUNT(DISTINCT CASE WHEN t2.offer_type_name='可选包' THEN offer_name END) ccc
FROM
zone_crmchk.dwd_sum_zhuliuxxsc t1
LEFT JOIN zone_crmchk.dwd_sum_zhuliuxxsc t2 ON t2.month < t1.month
GROUP BY
t1.month_id,
COUNT(DISTINCT CASE WHEN offer_type_name='主套餐' THEN offer_name END),
COUNT(DISTINCT CASE WHEN offer_type_name='促销' THEN offer_name END),
COUNT(DISTINCT CASE WHEN offer_type_name='可选包' THEN offer_name END);
```
这样修改后,查询语句应该就能正确执行了。