MySQL：关联查询实例记录

1.表与表之间的关联查询（

举例三个表，driver 司机表  driver_contract 司机承包表  driver_contract_stage 司机承包表附表（分三期缴费））

CREATE TABLE `driver` (
`driver_id` BIGINT(20) NOT NULL COMMENT '司机id',
`driver_name` varchar(20) DEFAULT NULL COMMENT '姓名',
`id_card` VARCHAR(45) DEFAULT NULL COMMENT '身份证号',
`company_name` VARCHAR(45) DEFAULT NULL COMMENT '公司名称',
`company_id` BIGINT(20) DEFAULT NULL COMMENT '车公司',
`nation` VARCHAR(45) DEFAULT NULL COMMENT '民族',
`birthday` DATE DEFAULT NULL COMMENT '出生日期',
`birth_place` VARCHAR(255) DEFAULT NULL COMMENT '户口地',
`mobile_number` VARCHAR(20) DEFAULT NULL COMMENT '手机',
`business_model` VARCHAR(4) DEFAULT NULL COMMENT '经营模式（1：公营，2：承包）',
`user_code` BIGINT(20) DEFAULT NULL,
`vehicle_compre_add_status` TINYINT(4) DEFAULT NULL COMMENT '车辆综合部新增状态 1.新增中 2.审批中 3.审批通过，-3.审批不通过',
`vehicle_compre_update_status` TINYINT(4) DEFAULT NULL COMMENT '车辆综合部更新状态 1.新增中 2.审批中 3.审批通过，-3.审批不通过',
`vehicle_tech_update_status` TINYINT(4) DEFAULT NULL COMMENT '车辆技术部更新状态 1.新增中 2.审批中 3.审批通过，-3.审批不通过',
`tour_compre_update_status` TINYINT(4) DEFAULT NULL COMMENT '旅发综合部更新状态 1.新增中 2.审批中 3.审批通过，-3.审批不通过',
`dimission_status` TINYINT(4) DEFAULT NULL COMMENT '离职状态 1.正常 2.审批中 3.审批通过，-3.审批不通过',
`blacklist_status` TINYINT(4) DEFAULT NULL COMMENT '黑名单状态 1.正常 2.审批中 3.审批通过，-3.审批不通过',
`driver_status` TINYINT(4) DEFAULT NULL COMMENT '司机状态 1.在职 2.离职 3.黑名单',
`create_by` BIGINT(20) DEFAULT NULL COMMENT '新增人id',
`create_time` datetime DEFAULT NULL COMMENT '新增时间',
`update_by` BIGINT(20) DEFAULT NULL COMMENT '更新人id',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
`entry_time` datetime DEFAULT NULL COMMENT '入职时间',
`departure_time` datetime DEFAULT NULL COMMENT '离职时间',
`blacklist_time` datetime DEFAULT NULL COMMENT '加入黑名单时间',
`blacklist_company_id` BIGINT(20) DEFAULT NULL COMMENT '加入黑名单时间',
`driver_license` VARCHAR(255) DEFAULT NULL COMMENT '驾驶证档案号',
`is_driver_bind_cellphone` TINYINT(4) DEFAULT '0' COMMENT '是否绑定手机',
PRIMARY KEY (`driver_id`),
KEY `idx_id_card` (`id_card`),
KEY `idx_mobile_number` (`mobile_number`),
KEY `idx_user_code` (`user_code`)

)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='司机表';


CREATE TABLE `driver_contract` (
`driver_id` BIGINT(20) NOT NULL,
`contract_id` BIGINT(20) DEFAULT NULL COMMENT '合同id',
`contract_version` int(11) DEFAULT NULL COMMENT '承包数据版本',
`total_amount` int(11) DEFAULT NULL COMMENT '总金额(单位分)',
`pay_type` int(11) DEFAULT NULL COMMENT '缴费类型(1一次性,2分期)',
`pay_date` datetime DEFAULT NULL COMMENT '缴费日期（一次性缴费才有该值）',
`oper_start_date` date DEFAULT NULL COMMENT '经营开始日期',
`oper_end_date` date DEFAULT NULL COMMENT '经营结束日期',
`create_name` VARCHAR(20) DEFAULT NULL,
`create_id` BIGINT(20) DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_name` VARCHAR(20) DEFAULT NULL,
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`update_id` BIGINT(20) DEFAULT NULL,
PRIMARY KEY (`driver_id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='司机承包信息';


CREATE TABLE `driver_contract_stage` (
`id` BIGINT(20) NOT NULL,
`driver_id` BIGINT(20) NOT NULL,
`contract_id` BIGINT(20) DEFAULT NULL COMMENT '合同id',
`period` int(11) DEFAULT NULL COMMENT '期数（1一期，2二期，3三期）',
`pay_date` datetime DEFAULT NULL COMMENT '缴费日期',
`deadline_pay_date` date DEFAULT NULL COMMENT '缴费截止日期',
`pay_amount` int(11) DEFAULT NULL COMMENT '缴费金额（单位分）',
`pay_complete` int(11) DEFAULT '0' COMMENT '是否缴费完成（0否1是）',
`overdue` int(11) DEFAULT '0' COMMENT '是否逾期（0否1是）',
PRIMARY KEY (`id`),
UNIQUE KEY `uni_period_id` (`period`,`driver_id`) USING BTREE
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='司机承包分期信息';

​​​​​​​

#缴费统计-下面提供两种方式：

SELECT SUM(IF (dcsf.pay_complete=1 ,dcsf.pay_amount,0))+
SUM(IF (dcsq.pay_complete=1 ,dcsq.pay_amount,0))+
SUM(IF ( dcsr.pay_complete=1,dcsr.pay_amount,0))+
SUM(IF(dc.pay_type = 1,dc.total_amount,0)) 已缴金额,
SUM(IF (dcsf.pay_complete=0 ,dcsf.pay_amount,0))+
SUM(IF (dcsq.pay_complete=0 ,dcsq.pay_amount,0))+
SUM(IF ( dcsr.pay_complete=0,dcsr.pay_amount,0)) 未缴金额
FROM driver d
LEFT JOIN driver_contract dc ON d.driver_id = dc.driver_id
LEFT JOIN driver_contract_stage dcsf ON dc.driver_id = dcsf.driver_id AND dcsf.period = 1
LEFT JOIN driver_contract_stage dcsq ON dc.driver_id = dcsq.driver_id AND dcsq.period = 2
LEFT JOIN driver_contract_stage dcsr ON dc.driver_id = dcsr.driver_id AND dcsr.period = 3
WHERE d.business_model = 2 AND dc.contract_version = 1 AND d.driver_status = 1;


#缴费统计
SELECT SUM(已缴金额),SUM(未缴金额) FROM (
SELECT
SUM(IF(dcs.pay_complete = 1, dcs.pay_amount,0)) 已缴金额 ,
SUM(IF(dcs.pay_complete = 0,dcs.pay_amount,0)) 未缴金额
FROM  driver_contract_stage dcs RIGHT JOIN driver_contract dc
ON dcs.driver_id = dc.driver_id
UNION
SELECT 
SUM(IF(dc.pay_type = 1,dc.total_amount,0))已缴金额,
0 AS 未缴金额
FROM driver_contract dc
) temp ;

主要是说明：

第一种方式  效率 优于  第二种方式，思维逻辑不同；

第二种方式使用了 UNION  结合两次派生的表的结果；

相关sql记录：

#-----------#################################综合统计#######################################------------------
#承包车占比
SELECT SUM(company_id in (453626921985642496, 453626921989836800, 453626921989836801)) as 'all', 
IFNULL(SUM(company_id=453626921985642496), 0) as '岗嘎(辆)',
IFNULL(SUM(company_id=453626921989836800), 0) as '悦吉(辆)',
IFNULL(SUM(company_id=453626921989836801), 0) as '悦腾(辆)'
FROM driver
WHERE business_model=2 and driver_status = 1;

#缴费类型
SELECT IFNULL(SUM(dc.pay_type = 1),0 ) AS '一次性缴费',IFNULL(SUM(dc.pay_type =2 ),0) AS '分期缴费' 
FROM driver_contract dc LEFT OUTER JOIN driver d
ON d.driver_id =dc.driver_id
WHERE d.business_model =2 AND d.driver_status = 1 AND dc.contract_version = 1 ;


#是否逾期（没有统计老数据）
SELECT IFNULL(SUM(dcsf.overdue=1 OR dcss.overdue=1 OR dcst.overdue=1), 0) as '逾期未缴费(辆)',
        IFNULL(SUM(dc.pay_type=1), 0) + IFNULL(SUM(dcsf.overdue=0 AND dcss.overdue=0 AND dcst.overdue=0),0) as '正常缴费(辆)'
        FROM driver d
        LEFT JOIN driver_contract dc ON d.driver_id = dc.driver_id
        LEFT JOIN driver_contract_stage dcsf ON dc.driver_id = dcsf.driver_id and dcsf.period = 1
        LEFT JOIN driver_contract_stage dcss ON dc.driver_id = dcss.driver_id and dcss.period = 2
        LEFT JOIN driver_contract_stage dcst ON dc.driver_id = dcst.driver_id and dcst.period = 3
        WHERE d.business_model = 2 AND dc.contract_version = 1 and d.driver_status = 1;


#是否逾期（没有统计老数据）

SELECT SUM(未逾期), SUM(逾期)
FROM 
(
SELECT SUM(temp.a=3) as 未逾期,
SUM(temp.a != 3) as 逾期
from
(
SELECT 
IFNULL(SUM(dcs.overdue = 0),0) as a,
IFNULL(SUM(dcs.overdue = 1),0) as b,
dcs.driver_id
FROM driver_contract_stage dcs  LEFT JOIN driver_contract dc
ON dcs.driver_id = dc.driver_id
WHERE dc.contract_version = 1
GROUP BY dcs.driver_id
) temp
union
SELECT sum(pay_type=1) as 未逾期,
0 as 逾期
FROM driver_contract 
)temp1;

#缴费统计


SELECT SUM(IF (dcsf.pay_complete=1 ,dcsf.pay_amount,0))+
SUM(IF (dcsq.pay_complete=1 ,dcsq.pay_amount,0))+
SUM(IF ( dcsr.pay_complete=1,dcsr.pay_amount,0))+
SUM(IF(dc.pay_type = 1,dc.total_amount,0)) 已缴金额,
SUM(IF (dcsf.pay_complete=0 ,dcsf.pay_amount,0))+
SUM(IF (dcsq.pay_complete=0 ,dcsq.pay_amount,0))+
SUM(IF ( dcsr.pay_complete=0,dcsr.pay_amount,0)) 未缴金额
FROM driver d
LEFT JOIN driver_contract dc ON d.driver_id = dc.driver_id
LEFT JOIN driver_contract_stage dcsf ON dc.driver_id = dcsf.driver_id AND dcsf.period = 1
LEFT JOIN driver_contract_stage dcsq ON dc.driver_id = dcsq.driver_id AND dcsq.period = 2
LEFT JOIN driver_contract_stage dcsr ON dc.driver_id = dcsr.driver_id AND dcsr.period = 3
WHERE d.business_model = 2 AND dc.contract_version = 1 AND d.driver_status = 1;


#缴费统计
SELECT SUM(已缴金额),SUM(未缴金额) FROM (
SELECT
SUM(IF(dcs.pay_complete = 1, dcs.pay_amount,0)) 已缴金额 ,
SUM(IF(dcs.pay_complete = 0,dcs.pay_amount,0)) 未缴金额
FROM  driver_contract_stage dcs RIGHT JOIN driver_contract dc
ON dcs.driver_id = dc.driver_id
UNION
SELECT 
SUM(IF(dc.pay_type = 1,dc.total_amount,0))已缴金额,
0 AS 未缴金额
FROM driver_contract dc
) temp ;


####################################按公司分组  统计 ###################################################################



#缴费类型(公司分组)
SELECT IFNULL(SUM(dc.pay_type = 1),0 ) AS '一次性缴费',IFNULL(SUM(dc.pay_type =2 ),0) AS '分期缴费' ,d.company_name
FROM driver_contract dc LEFT OUTER JOIN driver d
ON d.driver_id =dc.driver_id
WHERE d.business_model =2 AND d.driver_status = 1 AND dc.contract_version = 1 
GROUP BY d.company_id;

#是否逾期（没有统计老数据-分组公司）
SELECT IFNULL(SUM(dcsf.overdue=1 OR dcss.overdue=1 OR dcst.overdue=1), 0) as '逾期未缴费(辆)',
        IFNULL(SUM(dc.pay_type=1), 0) + IFNULL(SUM(dcsf.overdue=0 AND dcss.overdue=0 AND dcst.overdue=0),0) as '正常缴费(辆)',
				d.company_name
        FROM driver d
        LEFT JOIN driver_contract dc ON d.driver_id = dc.driver_id
        LEFT JOIN driver_contract_stage dcsf ON dc.driver_id = dcsf.driver_id and dcsf.period = 1
        LEFT JOIN driver_contract_stage dcss ON dc.driver_id = dcss.driver_id and dcss.period = 2
        LEFT JOIN driver_contract_stage dcst ON dc.driver_id = dcst.driver_id and dcst.period = 3
        WHERE d.business_model = 2 AND dc.contract_version = 1 and d.driver_status = 1
				GROUP BY d.company_id;

#缴费统计(公司分组)

SELECT SUM(IF (dcsf.pay_complete=1 ,dcsf.pay_amount,0))+
SUM(IF (dcsq.pay_complete=1 ,dcsq.pay_amount,0))+
SUM(IF ( dcsr.pay_complete=1,dcsr.pay_amount,0))+
SUM(IF(dc.pay_type = 1,dc.total_amount,0)) 已缴金额,
SUM(IF (dcsf.pay_complete=0 ,dcsf.pay_amount,0))+
SUM(IF (dcsq.pay_complete=0 ,dcsq.pay_amount,0))+
SUM(IF ( dcsr.pay_complete=0,dcsr.pay_amount,0)) 未缴金额,
d.company_name
FROM driver d
LEFT JOIN driver_contract dc ON d.driver_id = dc.driver_id
LEFT JOIN driver_contract_stage dcsf ON dc.driver_id = dcsf.driver_id AND dcsf.period = 1
LEFT JOIN driver_contract_stage dcsq ON dc.driver_id = dcsq.driver_id AND dcsq.period = 2
LEFT JOIN driver_contract_stage dcsr ON dc.driver_id = dcsr.driver_id AND dcsr.period = 3
WHERE d.business_model = 2 AND dc.contract_version = 1 AND d.driver_status = 1
GROUP BY d.company_id;