一道sql题，case when 配合 sum 求总和

逃离我推掉我的手 2022-08-31 02:21 123阅读 0赞

1、表结构，三张表，流程表wf\_task、法人表cust\_legal，订单表

CREATE TABLE `wf_task` (
      `id` varchar(32) NOT NULL,
      `buss_no` varchar(128) NOT NULL DEFAULT '' COMMENT '订单号',
      `cust_id` varchar(32) DEFAULT NULL COMMENT '客户ID',
      `cust_name` varchar(64) DEFAULT NULL COMMENT '客户名称',
      `task_type` varchar(2) NOT NULL COMMENT '任务类型',
      `task_name` varchar(64) DEFAULT NULL COMMENT '任务类型名称',
      `owner_id` varchar(32) DEFAULT NULL COMMENT '任务员ID',
      `owner_name` varchar(64) DEFAULT NULL COMMENT '任务员姓名',
      `begin_time` timestamp NULL DEFAULT NULL COMMENT '任务开始时间',
      `finish_time` timestamp NULL DEFAULT NULL COMMENT '任务完成时间',
      `pre_id` varchar(32) DEFAULT NULL COMMENT '前一个任务ID',
      `remark` varchar(256) DEFAULT NULL COMMENT '备注',
      `is_current` varchar(1) NOT NULL DEFAULT 'Y' COMMENT '是否有效：Y-有效，N-无效，已删除',
      `status` varchar(8) NOT NULL DEFAULT '00' COMMENT '任务状态：00-待处理，01-处理中，02-已提交（审批通过等正向操作），03-已拒绝，04-已退回，...',
      `creator` varchar(32) NOT NULL DEFAULT '' COMMENT '创建人员ID',
      `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      `editor` varchar(32) NOT NULL DEFAULT '' COMMENT '修改人员ID',
      `edit_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
      PRIMARY KEY (`id`),
      KEY `idx_buss_no` (`buss_no`),
      KEY `idx_task_type_status` (`task_type`,`status`),
      KEY `idx_buss_no_task_type_status` (`buss_no`,`task_type`,`status`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='任务流-任务节点表';
    
    
    
    
    
    
    
    CREATE TABLE `cust_legal` (
      `id` varchar(32) NOT NULL COMMENT '序列号',
      `buss_no` varchar(128) DEFAULT NULL COMMENT '业务编号',
      `partner_type` varchar(8) NOT NULL DEFAULT 'LEG' COMMENT '伙伴类型 NAT -自然人， LEG - 法人',
      `buss_type` varchar(8) NOT NULL DEFAULT 'BZ' COMMENT '伙伴类型 BZ -保证人， HG - 回购人',
      `identity_type` varchar(30) NOT NULL DEFAULT '' COMMENT '身份类型',
      `com_fullname` varchar(255) DEFAULT '' COMMENT '商业伙伴全称/回购人名称',
      `partner_serial` varchar(255) DEFAULT '' COMMENT '伙伴编码',
      `social_serial` varchar(255) DEFAULT '' COMMENT '统一社会信息代码',
      `cert_end_date_option` varchar(2) DEFAULT '' COMMENT '营业执照是否长期有效',
      `cert_start_date` timestamp NULL DEFAULT NULL COMMENT '证件生效时间',
      `cert_end_date` datetime DEFAULT NULL COMMENT '证件失效时间',
      `com_nature` varchar(8) DEFAULT '' COMMENT '公司性质',
      `com_industry` varchar(128) DEFAULT '' COMMENT '公司所属行业',
      `com_registered_capital` decimal(20,6) unsigned DEFAULT NULL COMMENT '注册资本（万元）',
      `com_establish_date` timestamp NULL DEFAULT NULL COMMENT '公司成立日期',
      `com_income` decimal(20,6) unsigned DEFAULT NULL COMMENT '营业收入/年营业额（万元）',
      `com_scope` varchar(1024) DEFAULT '' COMMENT '经营范围',
      `com_address` varchar(1024) DEFAULT '' COMMENT '营业地址',
      `contact_user` varchar(64) NOT NULL DEFAULT '' COMMENT '联系人',
      `contact_address` varchar(1024) NOT NULL DEFAULT '' COMMENT '联系地址',
      `contact_mobile` varchar(128) NOT NULL DEFAULT '' COMMENT '联系人电话',
      `contact_cert_no` varchar(128) NOT NULL DEFAULT '' COMMENT '联系人证件号',
      `com_members` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '从业人数',
      `agency_level` varchar(2) DEFAULT '' COMMENT '回购方经销商层级',
      `com_manager` varchar(128) DEFAULT '' COMMENT '回购方负责人',
      `actual_controller` varchar(32) DEFAULT '' COMMENT '实际控制人（评分卡使用）',
      `com_mobile` varchar(128) DEFAULT '' COMMENT '回购方联系电话/联系地址',
      `service_fee` decimal(10,4) unsigned DEFAULT NULL COMMENT '回购方咨询服务费金',
      `fee_require` varchar(2) DEFAULT 'N' COMMENT '是否收取服务费：N - 不收取， Y -收取',
      `total_assets` decimal(20,6) DEFAULT NULL COMMENT '资产总额（万元）',
      `com_scale` varchar(32) DEFAULT '' COMMENT '企业规模',
      `legal_person` varchar(128) DEFAULT '' COMMENT '法人代表',
      `legal_cert_type` varchar(30) NOT NULL DEFAULT '' COMMENT '法人证件类型',
      `legal_cert_no` varchar(128) DEFAULT '' COMMENT '法人证件号',
      `legal_cert_deadline` datetime DEFAULT NULL COMMENT '法人证件失效时间',
      `legal_mobile` varchar(128) DEFAULT '' COMMENT '法人电话',
      `legal_address` varchar(1024) DEFAULT '' COMMENT '法人地址',
      `account_holder` varchar(128) DEFAULT '' COMMENT '管户人',
      `bank_account` varchar(256) DEFAULT '' COMMENT '借款卡号',
      `partner_id` varchar(32) DEFAULT NULL COMMENT '伙伴ID',
      `status` varchar(2) DEFAULT '1' COMMENT '数据有效性',
      `creator` varchar(32) DEFAULT '' COMMENT '创建人',
      `editor` varchar(32) DEFAULT '' COMMENT '编辑人',
      `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
      `edit_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录修改时间',
      `sort_index` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '排序',
      `overdue_days` int(10) DEFAULT NULL COMMENT '逾期天数',
      `contact_has_black` varchar(2) DEFAULT NULL COMMENT '联系人是否黑名单： N：否 Y：是',
      `contact_black_info` varchar(500) DEFAULT NULL,
      `legal_has_black` varchar(2) DEFAULT NULL COMMENT '法人是否黑名单： N：否 Y：是',
      `legal_black_info` varchar(500) DEFAULT NULL,
      `legal_long_term` varchar(2) DEFAULT NULL COMMENT '法人证件是否长期有效:Y:是 N:否',
      `legal_cert_startline` datetime DEFAULT NULL COMMENT '法人证件生效日期',
      `contact_long_term` varchar(2) DEFAULT NULL COMMENT '联系人证件是否长期有效:Y:是 N:否',
      `contact_cert_startline` datetime DEFAULT NULL COMMENT '联系人证件生效日期',
      `contact_cert_deadline` datetime DEFAULT NULL COMMENT '联系人证件失效日期',
      `com_registered_address` varchar(1024) DEFAULT '' COMMENT '注册地址',
      `registered_has_business` varchar(2) DEFAULT '' COMMENT '注册地是否是营业地址 Y:是 N:否',
      `controller_cert_no` varchar(128) DEFAULT '' COMMENT '实际控制人身份证号',
      `controller_long_term` varchar(2) DEFAULT '' COMMENT '实际控制人证件是否长期有效 Y:是 N:否',
      `controller_cert_startline` datetime DEFAULT NULL COMMENT '实际控制人证件生效日期',
      `controller_cert_deadline` datetime DEFAULT NULL COMMENT '实际控制人证件失效日期',
      `controller_has_black` varchar(2) DEFAULT NULL COMMENT '实际控制人是否命中黑名单 Y:是 N:否',
      `controller_black_info` varchar(500) DEFAULT NULL COMMENT '实际控制人命中类型',
      `controller_mobile` varchar(128) DEFAULT '' COMMENT '实际控制人手机号码',
      `dealer_has_hg` varchar(2) DEFAULT '' COMMENT '经销商是否回购人',
      `query_type` varchar(2) DEFAULT NULL COMMENT 'Y：已查询同盾报告',
      `credit_balance` decimal(15,2) DEFAULT NULL COMMENT '授信余额',
      `loan_balance` decimal(15,2) DEFAULT NULL COMMENT '放款余额',
      `buss_balance` decimal(15,2) DEFAULT NULL COMMENT '业务余额',
      `legal_nation_code` varchar(2) DEFAULT '' COMMENT '法人民族代码',
      `legal_nation_value` varchar(8) DEFAULT '' COMMENT '法人民族值',
      `contact_nation_code` varchar(2) DEFAULT '' COMMENT '法人联系人民族代码',
      `contact_nation_value` varchar(8) DEFAULT '' COMMENT '法人联系人民族值',
      `controller_nation_code` varchar(2) DEFAULT '' COMMENT '法人实际控制人民族代码',
      `controller_nation_value` varchar(8) DEFAULT '' COMMENT '法人实际控制人民族值',
      `controller_query_type` varchar(2) DEFAULT NULL COMMENT 'Y：控制人已查询同盾报告',
      `credit_balance_bz` decimal(15,2) DEFAULT NULL COMMENT '保证人授信余额',
      `loan_balance_bz` decimal(15,2) DEFAULT NULL COMMENT '保证人放款余额',
      `buss_balance_bz` decimal(15,2) DEFAULT NULL COMMENT '保证人业务余额',
      `credit_balance_hg` decimal(15,2) DEFAULT NULL COMMENT '回购人授信余额',
      `loan_balance_hg` decimal(15,2) DEFAULT NULL COMMENT '回购人放款余额',
      `buss_balance_hg` decimal(15,2) DEFAULT NULL COMMENT '回购人业务余额',
      PRIMARY KEY (`id`),
      KEY `idx_buss_no` (`buss_no`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='法人-（回购人、保证人）';
    
    
    
    
    
    
    
    CREATE TABLE `orders` (
      `id` varchar(32) NOT NULL COMMENT '序列号',
      `lease_mode` varchar(8) DEFAULT '' COMMENT '租赁模式: ZZ02 -  直租，HZ01 - 回租',
      `buss_no` varchar(128) NOT NULL DEFAULT '' COMMENT '业务编号',
      `lease_model` varchar(64) DEFAULT '' COMMENT '租赁物规格型号',
      `place_use` varchar(3000) NOT NULL DEFAULT '' COMMENT '使用地点',
      `lease_term` int(10) unsigned DEFAULT '0' COMMENT '租赁期限（月）',
      `lease_time_day` int(10) DEFAULT '0' COMMENT '租赁期限 (天)',
      `first_payment` decimal(15,2) unsigned DEFAULT NULL COMMENT '首付款金额',
      `raise_funds` decimal(15,2) DEFAULT NULL COMMENT '融资金额',
      `lease_amount` decimal(15,2) unsigned DEFAULT NULL COMMENT '租金总额（元），含利息',
      `start_date` date DEFAULT NULL COMMENT '起租时间',
      `end_date` date DEFAULT NULL COMMENT '止租时间',
      `lease_rate` decimal(10,6) DEFAULT NULL COMMENT '租赁利率',
      `repay_rate` varchar(2) DEFAULT 'M' COMMENT '还款频率',
      `calc_type` varchar(10) DEFAULT NULL COMMENT '计息方式   DAY:按日计息   SCHEDULE:按期计息',
      `allowance_amt` decimal(15,2) DEFAULT '0.00' COMMENT '补贴金额',
      `allowance_date` date DEFAULT NULL COMMENT '补贴时间',
      `rebate_amount` decimal(15,2) DEFAULT '0.00' COMMENT '厂商返利金额',
      `rebate_firm_amount` decimal(15,2) DEFAULT '0.00' COMMENT '厂商贴息',
      `firm_rebate_intrest_date` varchar(10) NOT NULL DEFAULT '' COMMENT '厂商贴息时间',
      `other_expense` varchar(30) NOT NULL DEFAULT '0.00' COMMENT '返利厂商',
      `other_expense_date` varchar(10) NOT NULL DEFAULT '' COMMENT '返利厂商时间',
      `risk_capital` decimal(15,2) DEFAULT '0.00' COMMENT '承租人风险金',
      `risk_capital_other` decimal(15,2) DEFAULT '0.00' COMMENT '其他风险金',
      `payment_way` varchar(32) DEFAULT '' COMMENT '租金支付方式',
      `deposit` decimal(10,2) unsigned NOT NULL DEFAULT '1.00' COMMENT '留购价格',
      `settle_ahead` decimal(15,2) DEFAULT '0.00' COMMENT '提前结清',
      `earning_rate` decimal(12,8) DEFAULT NULL COMMENT '内部收益率',
      `task_type` varchar(2) NOT NULL DEFAULT '' COMMENT '任务流程号',
      `task_status` varchar(6) DEFAULT '' COMMENT '流程状态',
      `has_sign_contract` varchar(2) DEFAULT 'N' COMMENT '是否已签订合同， N - 否， Y - 是',
      `pass_loan_approve` varchar(2) DEFAULT 'N' COMMENT '贷款审批是否通过， N - 否， Y - 是',
      `pass_lend_approve` varchar(2) DEFAULT 'N' COMMENT '放款审批是否通过， N - 否， Y - 是',
      `lend_confirm` varchar(2) DEFAULT 'N' COMMENT '是否已经放款确认， N - 否， Y - 是',
      `status` varchar(2) DEFAULT '1' COMMENT '数据是否有效， 0 - 否， 1 - 是',
      `creator` varchar(32) DEFAULT '' COMMENT '创建人',
      `editor` varchar(32) DEFAULT '' COMMENT '修改人',
      `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
      `edit_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录修改时间',
      `depart_id` varchar(32) DEFAULT NULL COMMENT '部门id',
      `sponsor_id` varchar(32) DEFAULT NULL COMMENT '主办人id',
      `jointly_id` varchar(32) DEFAULT NULL COMMENT '协办人id',
      `sponsor_name` varchar(32) DEFAULT NULL COMMENT '主办人姓名',
      `jointly_name` varchar(32) DEFAULT NULL COMMENT '协办人姓名',
      `come_from` varchar(10) DEFAULT 'app' COMMENT '订单来源:app,import',
      `leasee_risk_date` date DEFAULT NULL COMMENT '承租人风险金到账时间',
      `dealer_risk_date` date DEFAULT NULL COMMENT '经销商风险金到账时间',
      `audit_status` varchar(2) DEFAULT NULL COMMENT '自动审批状态：1-通过  2-审慎审核  3-一次拒决 4-二次拒绝',
      `product_type` varchar(2) DEFAULT NULL COMMENT '产品类型：00-正常业务 01-洋马（哈银租审字【2021】2-3号） 02-久保田（哈银租审字【2021】8-2号） 03-雷沃（哈银租审字【2021】8-1号） 04-沃得（哈银租审字【2021】2-4号）',
      `write_off_flag` varchar(10) NOT NULL DEFAULT 'NOT' COMMENT '核销与否,NOT:未核销;FULL:已核销',
      PRIMARY KEY (`id`),
      UNIQUE KEY `uniq_buss_no` (`buss_no`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='业务订单表rr';

2、需求：

流程表 wf\_task 如果字段任务类型task\_type是在51节点（已放款状态），并且状态status是02，is\_current = 'Y' 有效的时候，我们就认为该笔订单，已经放款了，否则，就没有放款，查询累计放款总额，融资金额（融资金额不受放款状态影响）

3、sql如下：

SELECT
    	sum( CASE WHEN w.create_time = '' OR w.create_time IS NULL THEN 0 ELSE o.raise_funds END ) AS loadFunds,
    	sum( o.raise_funds ) AS raiseFunds 
    FROM
    	orders o
    	INNER JOIN ( SELECT social_serial, buss_no FROM cust_legal WHERE `status` = '1' AND social_serial IN ( '/FkOfEtVS2rBre6PDgVNbw==' ) AND buss_type = 'HG' ) nat ON o.buss_no = nat.buss_no
    	LEFT JOIN ( SELECT buss_no, create_time FROM wf_task WHERE `status` = '02' AND task_type = '51' AND is_current = 'Y' ) w ON w.buss_no = o.buss_no 
    WHERE
    	o.`status` = '1' 
    	AND o.task_type != '' 
    	AND o.task_type IS NOT NULL

4、执行结果如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlanVzdGRvaXQ_size_16_color_FFFFFF_t_70]: /images/20220829/2fa45437473a435a93852373695fcd78.png