sql 名词解析
针对这个 sql 语句进行解析
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 SELECT 2 a.* 3 , b.L12_create_time L1第一次转L2的时间 4 , ROUND(c.L1_pending/60, 2) "等待总时长L1(分钟)" 5 , ROUND(d.L1forward_time/60, 2) "转移时长L1(分钟)" 6 7 from 8 ( 9 SELECT 10 a.id 11 , a.tn 12 , a.create_time 13 , max(case when IFNULL(f.id,0)<>0 then f.create_time ELSE NULL END) "关闭时间" 14 , max(case when b.field_id=86 then b.value_text else null end) 工单来源 15 , max(case when b.field_id=255 then b.value_text else null end) 模块 16 , max(case when b.field_id=240 then b.value_text else null end) 解决团队 17 , g.name 服务水平协议 18 FROM 19 ticket a 20 left join dynamic_field_value b on a.id=b.object_id and b.field_id IN(86,255,240) 21 right JOIN ticket_history f on a.id = f.ticket_id AND (f.name LIKE '%closed successful%' OR f.name LIKE '%首次电话解决%'OR f.name LIKE '%用户无回应%') 22 LEFT JOIN sla g ON a.sla_id=g.id 23 right JOIN dynamic_field_value e ON a.id=e.object_id AND e.field_id = 79 24 where 25 f.create_time BETWEEN '2022-02-01' AND '2022-03-01' AND 26 e.value_text LIKE '%D-Flow%' 27 group by a.tn 28 , a.create_time 29 , g.name 30 ) a 31 -- L1第一次转L2的时间 32 left join 33 ( 34 select 35 a.ticket_id 36 , min(a.create_time) L12_create_time 37 from 38 ticket_all_working_time a 39 where 40 SUBSTRING_INDEX(a.name, '%%', -2) = 'D-Flow L1%%75' 41 group by a.ticket_id 42 ) b on a.id=b.ticket_id 43 44 45 -- 等待总时长L1 46 left join 47 ( 48 SELECT 49 a.ticket_id 50 , sum(a.working_time) L1_pending 51 from 52 ticket_all_working_time a 53 left join 54 ( 55 select 56 a.ticket_id 57 , min(a.create_time) L12_create_time 58 from 59 ticket_all_working_time a 60 where 61 SUBSTRING_INDEX(a.name, '%%', -2) = 'D-Flow L1%%75' 62 group by a.ticket_id 63 ) b on a.ticket_id=b.ticket_id 64 inner join 65 ( 66 select 67 a.id 68 , a.name state_name 69 , b.name state_type_name 70 from 71 ticket_state a 72 , ticket_state_type b 73 WHERE 74 a.type_id=b.id 75 ) c on a.state_id = c.id 76 where 77 a.create_time <= b.L12_create_time 78 and c.state_type_name='pending auto' 79 and a.queue_id=75 80 group by a.ticket_id 81 ) c on a.id=c.ticket_id 82 -- 转移时长L1 83 left join 84 ( 85 SELECT 86 a.ticket_id 87 , sum(a.working_time) L1forward_time 88 from 89 ticket_all_working_time a 90 left join 91 ( 92 select 93 a.ticket_id 94 , min(a.create_time) L12_create_time 95 from 96 ticket_all_working_time a 97 where 98 SUBSTRING_INDEX(a.name, '%%', -2) = 'D-Flow L1%%75' 99 group by a.ticket_id 100 ) b on a.ticket_id=b.ticket_id 101 inner join 102 ( 103 select 104 a.id 105 , a.name state_name 106 , b.name state_type_name 107 from 108 ticket_state a 109 , ticket_state_type b 110 WHERE 111 a.type_id=b.id 112 ) c on a.state_id = c.id 113 where 114 a.create_time <= b.L12_create_time 115 and c.state_type_name<>'pending auto' 116 and a.queue_id=75 117 group by a.ticket_id 118 ) d on a.id=d.ticket_id
解析如下:
round(x,d) : x指要处理的数,d是指保留几位小数
比如:
ROUND(c.L1_pending/60, 2)
把 c.L1_pending/60 运算后保留两位小数
L12 :L1第一次转L2的时间
L1_pending:L1总时长,单位秒
L23:L2第一次转L3的时间
L23_pending:等待总时长L2,单位秒
秒转为分钟:
L23_pending/60 ,单位分钟
L23_working_time:转移时长L2,单位秒
MAX()函数:返回一组值中的最大值,如果有多个行,只取最大的那一行作为结果
比如:
max(case when IFNULL(f.id,0)<>0 then f.create_time ELSE NULL END) "关闭时间"
在 (如果 f.id 不等于0取创建时间 f.create_time ,如果 f.id 等于0 取NULL)这里面取最大值,
SELECT
a.id
, a.tn
, a.create_time
, case when IFNULL(f.id,0)<>0 then f.create_time ELSE NULL END "关闭时间"
, case when b.field_id=86 then b.value_text else null end 工单来源
, case when b.field_id=255 then b.value_text else null end 模块
, case when b.field_id=240 then b.value_text else null end 解决团队
, g.name 服务水平协议
FROM
ticket a
left join dynamic_field_value b on a.id=b.object_id and b.field_id IN(86,255,240)
right JOIN ticket_history f on a.id = f.ticket_id AND (f.name LIKE '%closed successful%' OR f.name LIKE '%首次电话解决%'OR f.name LIKE '%用户无回应%')
LEFT JOIN sla g ON a.sla_id=g.id
right JOIN dynamic_field_value e ON a.id=e.object_id AND e.field_id = 79
where
f.create_time BETWEEN '2022-02-01' AND '2022-03-01' AND
e.value_text LIKE '%D-Flow%'
SELECT
a.id
, a.tn
, a.create_time
, max(case when IFNULL(f.id,0)<>0 then f.create_time ELSE NULL END) "关闭时间"
, max(case when b.field_id=86 then b.value_text else null end) 工单来源
, max(case when b.field_id=255 then b.value_text else null end) 模块
, max(case when b.field_id=240 then b.value_text else null end) 解决团队
, g.name 服务水平协议
, p.pending_start_time 第一次挂起时间
FROM
ticket a
left join dynamic_field_value b on a.id=b.object_id and b.field_id IN(86,255,240)
right JOIN ticket_history f on a.id = f.ticket_id AND (f.name LIKE '%closed successful%' OR f.name LIKE '%首次电话解决%'OR f.name LIKE '%用户无回应%')
LEFT JOIN sla g ON a.sla_id=g.id
left join ticket_pending_and_work_time p on p.ticket_id=a.id
right JOIN dynamic_field_value e ON a.id=e.object_id AND e.field_id = 79
where
f.create_time BETWEEN '2022-02-01' AND '2022-03-01' AND
e.value_text LIKE '%D-Flow%'
group by a.tn
, a.create_time
, g.name
MIN()函数:返回一组值中的最小值,如果有多个行,会只取最小的那一行作为结果
SUM()函数:返回数值列的总数。如果有多个行,会合并行作为结果
比如:
SELECT
a.ticket_id
,a.working_time
from
ticket_all_working_time a
SELECT
a.ticket_id
,sum(a.working_time) L1_pending
from
ticket_all_working_time a
group by a.ticket_id
CASE WHEN 多个判断条件
IFNULL(expression, alt_value):如果第一个参数的表达式 expression 为 NULL,则返回第二个参数的备用值。
<> :不等于
例如:IFNULL(f.id,0)<>0
如果 f.id 为NULL则返回0,再去比较结果是否等于 0
CASE WHEN [expr] THEN [result1]…ELSE [default] END:搜索函数可以写判断,并且搜索函数只会返回第一个符合条件的值,其他 case 被忽略
例如:case when IFNULL(f.id,0)<>0 then f.create_time ELSE NULL END
IFNULL(f.id,0):
如果 f.id 为NULL,就返回0,否则返回f.id
IFNULL(f.id,0)<>0:
f.id 不等于0
then f.create_time:如果是,则返回f.create_time
ELSE NULL END:否则返回NULL
OR 满足一个条件即刻
例如:f.name LIKE '%closed successful%' OR f.name LIKE '%首次电话解决%'OR f.name LIKE '%用户无回应%'
f.name 里面包含 closed successful 或者 f.name 里面包含 首次电话解决 或者f.name 里面包含 用户无回应
AND 同时满足前后两个条件
LIKE 匹配/模糊匹配
LEFT JOIN(左连接):获取左表所有记录,即使右表没有对应匹配的记录。
INNER JOIN(内连接,或等值连接):获取两个表中字段匹配关系的记录。
RIGHT JOIN(右连接):与 LEFT JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录。
SUBSTRING_INDEX(“待截取有用部分的字符串”,“截取数据依据的字符”,截取字符的位置N)
例如:
SUBSTRING_INDEX(a.name, '%%', -2) = 'D-Flow L1%%75'
把 a.name 从倒数第二个 %% 开始截取,并且截取结果等于 D-Flow L1%%75
group by 当sql语句中有聚合函数时,非聚合字段必须用group by
比如:
select
a.ticket_id
,a.create_time
, min(a.create_time) L12_create_time
from
ticket_all_working_time a
where
SUBSTRING_INDEX(a.name, '%%', -2) = 'D-Flow L1%%75'
group by a.ticket_id,a.create_time
语句中包含聚合函数 min(),所以对于非聚合函数的 a.ticket_id 和 a.create_time 必须用 group by 对结果集进行分组
如果不加,就会报错如下:
[Err] 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sc_otrs.a.create_time' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by