失眠网 > mysql 统计报表界面查询案例经典sql语句

mysql 统计报表界面查询案例经典sql语句

时间：2021-01-06 00:17:54

要照着一个界面实现一个报表及其报表统计的查询功能。

界面如下：

而我，则要只通过一条sql语句，来完成所有字段的展示。

根据界面得知，

查询条件有4个，分别为商机名称，单位简称，开始时间，结束时间。

排序条件有5个，分别为派单数，外呼数，呼通数，有意向数，呼叫时长

要查询出的字段有14个。

现在，我要照着这个界面实现这个功能，方案是通过一条sql语句来实现，而不是在java层做处理

那么，既然要实现，我们就需要先清楚这之间的字段关系，

首先我先介绍案例中，我们要用到的4张表

分别为business,active_data,call_record,sys_dept

他们的结构如下：

CREATE TABLE `business` (`id` int(11) NOT NULL AUTO_INCREMENT,`active_id` bigint(20) DEFAULT NULL COMMENT '活动ID',`active_name` varchar(255) DEFAULT NULL COMMENT '活动名称',`start_time` datetime DEFAULT NULL COMMENT '开始时间',`end_time` datetime DEFAULT NULL COMMENT '结束时间',`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',`busi_desc` varchar(500) DEFAULT NULL COMMENT '商机描述',`creator` varchar(30) DEFAULT NULL COMMENT '创建人',`recover_times` int(255) DEFAULT NULL COMMENT '回收次数',`depart_id` bigint(20) DEFAULT NULL COMMENT '部门Id',`update_time` datetime DEFAULT NULL COMMENT '更新时间',`activity_id` int(11) DEFAULT NULL COMMENT '智慧营销相关 id',`status` int(2) DEFAULT NULL COMMENT '智慧营销相关在线状态 0:上线 1:下线',`user_id` bigint(20) DEFAULT NULL COMMENT '智慧营销相关用户id',`del_flag` int(2) DEFAULT NULL COMMENT '是否失效: 0:有效 1:失效',`back_state` int(10) DEFAULT NULL COMMENT '回收状态：1是未回收，2是回收',PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=97 DEFAULT CHARSET=utf8mb4 COMMENT='活动表';

CREATE TABLE `active_data` (`id` bigint(20) NOT NULL AUTO_INCREMENT,`active_id` bigint(20) DEFAULT NULL COMMENT '活动ID',`active_name` varchar(255) DEFAULT NULL COMMENT '活动名称',`mobile` varchar(255) DEFAULT NULL COMMENT '派单号码',`speech` text COMMENT '话术',`sales_id` varchar(255) DEFAULT NULL COMMENT '销售品ID，多个逗号隔开',`ds` int(11) DEFAULT NULL COMMENT '地市',`qs` int(11) DEFAULT NULL COMMENT '区县',`zj` int(11) DEFAULT NULL COMMENT '支局',`yyt` int(11) DEFAULT NULL COMMENT '营业厅',`xq` varchar(255) DEFAULT NULL COMMENT '小区',`target_land_id` varchar(255) DEFAULT NULL COMMENT '目标对象本地网ID',`order_id` varchar(255) DEFAULT NULL,`contact_order_id` varchar(255) DEFAULT NULL COMMENT '工单ID',`contact_nbr` varchar(255) DEFAULT NULL COMMENT '客户联系方式（宽带号无法外呼）',`target_obj_nbr` varchar(255) DEFAULT NULL COMMENT '客户实例ID',`jz` varchar(255) DEFAULT NULL COMMENT '价值，存在价值排序可能',`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',`batch_id` varchar(255) DEFAULT NULL COMMENT '波次id',`state` int(2) DEFAULT NULL COMMENT '数据状态(0:未分配 1：省集中资源池 2：14市资源池 3：市集中资源池 4：市虚拟资源池 5：技能组)',`update_time` datetime DEFAULT NULL COMMENT '更新时间',`update_opt` varchar(255) DEFAULT NULL COMMENT '更新操作描述',`seat_num` varchar(50) DEFAULT NULL COMMENT '坐席账号',`city_code` varchar(255) DEFAULT NULL COMMENT '地市编码',`is_called` int(10) DEFAULT NULL COMMENT '1是接通，0是未接通',`order_status` int(10) DEFAULT NULL COMMENT '1是回单，2是未回单，3是回单失败，4正在执行回单',`product_name` varchar(100) DEFAULT NULL COMMENT '销售品名称',`fail_reson` varchar(255) DEFAULT NULL COMMENT '回单失败原因',`user_id` bigint(20) DEFAULT NULL COMMENT '用户id',`back_state` int(10) DEFAULT NULL COMMENT '回收状态：1是未回收，2是回收',`call_status` int(10) DEFAULT NULL COMMENT '外呼状态：1是未外呼，2是正在外呼，3是外呼完成',PRIMARY KEY (`id`),KEY `ds_index` (`ds`,`state`),KEY `qs_index` (`qs`,`state`),KEY `zj_index` (`zj`,`state`),KEY `yyt_index` (`yyt`,`state`)) ENGINE=InnoDB AUTO_INCREMENT=2572 DEFAULT CHARSET=utf8mb4 COMMENT='原始活动清单表';

CREATE TABLE `call_record` (`id` int(11) NOT NULL AUTO_INCREMENT,`caller_num` varchar(50) DEFAULT NULL COMMENT '主叫',`callee_num` varchar(50) DEFAULT NULL COMMENT '被叫号码',`call_type` int(11) NOT NULL DEFAULT '4' COMMENT '外呼类型1：派单外呼，2:双呼，3：预测外呼，4：拨号盘外呼，5：统一派单，6：放弃呼叫,8:呼入',`seat_num` bigint(15) DEFAULT NULL COMMENT '坐席号,也就是fs上保存的坐席号和sys_user_fs表的fs_user_id对应',`user_id` int(11) DEFAULT NULL COMMENT '用户id',`active_id` bigint(20) DEFAULT NULL COMMENT '活动id',`project_list_id` int(11) NOT NULL DEFAULT '0' COMMENT '数据清单id',`callee_uuid` varchar(255) DEFAULT NULL COMMENT '被叫uuid',`caller_uuid` varchar(255) NOT NULL COMMENT '主叫uuid',`begin_time` datetime DEFAULT NULL COMMENT '开始时间',`answer_time` datetime DEFAULT NULL COMMENT '应答时间',`end_time` datetime DEFAULT NULL COMMENT '结束时间',`isanswer` int(11) DEFAULT NULL COMMENT '是否接通0，未接通，1接通',`talk_time` int(11) DEFAULT NULL COMMENT '通话时长',`hangup_case` varchar(255) DEFAULT NULL COMMENT '挂断原因',`record_path` varchar(500) DEFAULT NULL COMMENT '录音地址',`create_date` datetime DEFAULT NULL COMMENT '创建时间',`ancestors` varchar(500) DEFAULT NULL COMMENT '组织部门ID的所有父类',`org_id` bigint(20) DEFAULT NULL COMMENT '组织部门ID',`datatype` int(11) DEFAULT '0' COMMENT '0:本平台，1：派单，',`confirm` int(2) DEFAULT '2' COMMENT '按键确认结果0:营销失败，1:营销成功，2:未营销；3:FS请求失败',`confirm_time` datetime DEFAULT NULL COMMENT '按键时间',`status` int(11) DEFAULT NULL COMMENT '数据操作状态 0:尚未通话 1:结束按键确认 2:结束通话',`identity_check` int(2) DEFAULT '2' COMMENT '身份验证结果：0：验证失败，1：验证成功；2:未验证',`back_state` int(2) DEFAULT NULL COMMENT '反馈状态，1，反馈成功，5反馈中，',`terminal_type` int(2) DEFAULT '0' COMMENT '终端类型0:普通外呼(ios,android,pc三者其一) 1:双呼（phone+pc) 2:IOS, 3:Android, 4:PC,',`dirctor` int(2) DEFAULT NULL COMMENT '0:呼出，1：呼入',`sharding_key` varchar(20) DEFAULT NULL COMMENT '分表键：按月分表： 03',`record_score` varchar(255) DEFAULT NULL COMMENT '录音评分',`score_remark` varchar(255) DEFAULT NULL COMMENT '录音质检备注',`record_qc_date` datetime DEFAULT NULL COMMENT '录音质检时间',`sync_batch_id` bigint(25) DEFAULT NULL COMMENT '同步fs数据时生成的批号id',`batch_id` bigint(20) DEFAULT NULL COMMENT '波次ID',`is_cooperation` int(10) DEFAULT NULL COMMENT '用户意向：1: 预约办理 5：特派划小 0：拒绝办理（需要在调用外部接口后同步更新此字段）',`pre_accept` int(11) DEFAULT NULL COMMENT '是否受理成功：0：未受理，1：已受理（需要在调用外部接口后同步更新此字段）',`pre_accept_date` datetime DEFAULT NULL COMMENT '受理时间（需要在调用外部接口后同步更新此字段）',PRIMARY KEY (`id`),KEY `create_date` (`create_date`),KEY `callee_uuid` (`callee_uuid`),KEY `caller_uuid` (`caller_uuid`),KEY `caller_num` (`caller_num`),KEY `callee_num` (`callee_num`),KEY `org_id` (`org_id`,`create_date`),KEY `project_list_id` (`project_list_id`,`create_date`),KEY `project_id` (`active_id`,`batch_id`) USING BTREE,KEY `user_id_create_date` (`create_date`,`id`) USING BTREE,KEY `user_id` (`id`,`user_id`,`isanswer`,`talk_time`,`call_type`,`is_cooperation`,`sharding_key`,`create_date`,`pre_accept`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=1000496 DEFAULT CHARSET=utf8 COMMENT='通话记录';

CREATE TABLE `sys_dept` (`dept_id` bigint(20) NOT NULL COMMENT '部门id',`parent_id` bigint(20) DEFAULT '0' COMMENT '父部门id',`ancestors` varchar(500) DEFAULT '' COMMENT '祖级列表',`dept_name` varchar(30) DEFAULT '' COMMENT '部门名称',`dept_code` varchar(255) DEFAULT '' COMMENT 'Z：集中，X：虚拟，Y：营销区域',`order_num` int(4) DEFAULT '0' COMMENT '显示顺序',`leader` varchar(20) DEFAULT NULL COMMENT '负责人',`phone` varchar(11) DEFAULT NULL COMMENT '联系电话',`email` varchar(50) DEFAULT NULL COMMENT '邮箱',`status` char(1) DEFAULT '0' COMMENT '部门状态（0正常 1停用）',`del_flag` char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',`create_by` varchar(64) DEFAULT '' COMMENT '创建者',`create_time` datetime DEFAULT NULL COMMENT '创建时间',`update_by` varchar(64) DEFAULT '' COMMENT '更新者',`update_time` datetime DEFAULT NULL COMMENT '更新时间',`remark` varchar(500) DEFAULT NULL COMMENT '备注',`mkt_lo_id` bigint(20) DEFAULT NULL COMMENT '营销区域（暂时不需要）',`level` int(10) DEFAULT NULL COMMENT '部门层级',`project_id` varchar(20) DEFAULT NULL COMMENT '创建公司请求fs得到的id',`dept_source` int(10) DEFAULT NULL COMMENT '来源途径，1为本地，2为同步过来的数据',`land_id` int(10) DEFAULT NULL COMMENT '分公司ID',`bss_update_date` datetime DEFAULT NULL COMMENT '同步数据平台数据的更新时间',PRIMARY KEY (`dept_id`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='部门表';

而我们要查出的字段有14个之多，它们之间的关联关系是怎样的？下面我来一一介绍。

14 总成功率（百分比），

由意向数，二次确认为1率，呼通数，接通成功数四者的总和除以外呼数和派单，再乘以100得出总成功率

13 接通成功率（百分比），

有call_record表的isanswer字段。是否接通0，未接通，1接通。
查出call_record表isanswer字段值为1的。
然后除以呼通数，再乘以100得出接通成功率结果。

12 ’二次确认为1率’（百分比），

call_record表的confirm字段。
查出二次确认为1的条数，然后除以二次确认数再乘以100.

11 二次确认数（条）。

没有二次确认的情况下，call_record表的confirm_time字段为空，以及Conirm字段为2（未营销）
那么conirm不为2就为二次确认了。

10 有意向数（条）。

call_record表is_cooperation字段。用户意向：1: 预约办理 5：特派划小 0：拒绝办理（需要在调用外部接口后同步更新此字段）

9 有意向率（百分比）

有意向数，除以外呼数，再乘以100

8 通话时长（总共全部通话时长，单位为秒）

call_record表talk_time字段

7 昨日外呼数（条）

根据call_record表的create_date字段来取时间

6 呼通率（百分比）

呼通数除以外呼数，再乘以100为百分比结果

5 呼通数（条）

就以call_record表的‘begin_time’为准，如果存在开始时间，则为一条呼通数。

4 外呼数（条）。

根据call_record表的条目数量。

3 派单数（条）。

根据business表关联查active_data表，查指定active_id，通过count得出。

2 单位名称

根据 business 表的depart_id，去查sys_dept表的dept_name

1 商机名称

business表的active_name。

注意：
business的active_id对应active_data表的的active_id是一对多的关系。而active_data表的active_id对应call_record表的active_id又是一对多的关系。

知道了关联关系，那么一切都好弄了。

大家要记住的是，如果要做一个复杂的查询，先不要想这个sql语句的结构该怎么弄、是写一条语句进而无需在java业务层处理、还是写多条语句最终在java层处理实现等等这类问题；

而是要先想着各个字段在这些表之间的关联关系，先搞清楚前端界面需要的字段是怎么来的，再去想怎么去写sql语句。

那么，搞清楚了字段的关联关系后，我用了一条sql语句来实现了查询。

这个查询中，主表为business，其余三个左外连接查询则是另外三张表。

sql语句如下：

SELECTb.active_name as activeName, -- 活动名称sd.dept_name as deptName,-- 所属部门IFNULL(ad.dispatchSum,'0') as dispatchSum, -- 派单数IFNULL(cr.outbounSum,0) as outbounSum, -- 外呼数IFNULL(cr.callSum,0) as callSum, -- 呼通数CASE-- 呼通率。判断内容：如果外呼数为0，则直接呼通率为0。反之，则呼通数除以外呼数，再乘以100得出呼通率。IFNULL(cr.outbounSum,0) WHEN 0 THEN 0 ELSE ROUND(cr.callSum / cr.outbounSum,2) * 100END callCate,IFNULL(cr.yesterdaySum,0) as yesterdaySum, -- 昨日外呼数IFNULL(cr.talkTimeSum,0) as talkTimeSum, -- 通话时长IFNULL(cr.cooperationSum,0) as cooperationSum,-- 有意向数CASE -- 有意向率。判断内容：如果外呼数为0，则直接有意向率为0。反之，则有意向数除以外呼数，再乘以100得出有意向率。IFNULL(cr.outbounSum,0) WHEN 0 THEN 0 ELSE ROUND(cr.cooperationSum / cr.outbounSum,2) * 100 END cooperationCate,IFNULL(cr.confirmSum,0) as confirmSum, -- 二次确认数。CASE -- 二次确认为1率。判断内容：如果二次确认数为0，则直接二次确认为1率为0。反之，则二次确认为1数除以二次确认数，再乘以100得出二次确认为1率。IFNULL(cr.confirmSum,0) WHEN 0 THEN 0 ELSE ROUND(cr.confirmInYesCate / cr.confirmSum,2) * 100 END confirmInYesCate,CASE -- 接通成功率。判断内容：如果呼通数为0，则接通成功率直接为0.反之，则接通成功数除以呼通数，再乘以100得出接通成功率。IFNULL(cr.callSum,0) WHEN 0 THEN 0 ELSE ROUND(cr.isAnswerSum / cr.callSum,2) * 100 END isAnswerSum,CASE -- 总成功率。判断内容：如果外呼数为0，则总成功率直接为0。反之，由意向数，二次确认为1率，呼通数，接通成功数四者的总和除以外呼数和派单，再乘以100得出总成功率。IFNULL(cr.outbounSum,0) WHEN 0 THEN 0 ELSE ROUND( (cr.cooperationSum+cr.confirmInYesCate+cr.callSum+cr.isAnswerSum) / (ad.dispatchSum+cr.outbounSum),2) * 100 END allcallsuccessCateFROM business AS bLEFT JOIN -- 处理派单表派单数(select COUNT(id) as dispatchSum,active_id from active_data where active_id IS NOT NULL GROUP BY active_id) as adON b.active_id = ad.active_idLEFT JOIN -- 处理通话清单表外呼数(select COUNT(id) as outbounSum,active_id,COUNT(begin_time) as callSum,COUNT((TO_DAYS(NOW()) - TO_DAYS(create_date) = 1)=1 or null) as yesterdaySum,SUM(talk_time) as talkTimeSum,COUNT(is_cooperation = 1 or null) as cooperationSum,COUNT(confirm != 2 or null) as confirmSum,COUNT(confirm = 1 or null) as confirmInYesCate,COUNT(isanswer = 1 or null) as isAnswerSumfrom call_record GROUP BY active_id) as crON cr.active_id = ad.active_idLEFT JOIN -- 部门表(select dept_name,dept_id from sys_dept) as sd on sd.dept_id = b.depart_idWHERE b.active_name LIKE CONCAT('%','商机','%') -- 模糊查询条件商机名称AND sd.dept_name LIKE CONCAT('%','甘肃','%') -- 模糊查询条件部门名称AND b.start_time >= '-08-25'-- 开始日期AND b.end_time <= '-09-20'-- 结束日期ORDER BY outbounSum -- 外呼数

而我之所以能通过一条sql语句来实现这么复杂的查询，完全是利用了mysql的函数来实现的。

下面我来挑几个介绍。

IFNULL(exp,exp)函数,

相当于三元表达式，如果左边有值则用左边的，如果左边没值则用右边的。

例如:

select name,IFNULL(level,0) as now_level from person;