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、执行结果如下: