sql 名詞解析

2022 年 3 月 9 日
筆記
JAVA, MySQL相關

針對這個 sql 語句進行解析

  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

View Code

解析如下：

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

Tags: JAVA MySQL相關

sql 名詞解析

VirMach 便宜 VPS

QNews

sql 名詞解析

分享此文：

Related Posts

深度工作第3章 深度工作是有意義的

Tomcat雙擊startup.bat閃退的原因及解決方式

工作後，你悟出什麼職場道理？

WPF優秀組件推薦之FreeSpire

VirMach 便宜 VPS

QNews

熱門搜尋

深度工作第3章深度工作是有意義的