“滿(mǎn)意度表”記錄了教師和學(xué)生對課程的樣分??意度用戶(hù)滿(mǎn)意程度?！笆俏鲇梅駶M(mǎn)意”列里是老師和學(xué)生對課程的評價(jià)，其中“是戶(hù)滿(mǎn)”表示教師和學(xué)生都滿(mǎn)意。(′Д` )

“ 用戶(hù)表”記錄了學(xué)校教師和學(xué)生的分析信息。每個(gè)用戶(hù)有唯一鍵 “編號”，滿(mǎn)意“是度的大思否在系統”表示這個(gè)用戶(hù)是否還在這所學(xué)校里，“角色”表示這個(gè)人是樣分意度用戶(hù)學(xué)生還是教師。

兩個(gè)表的析用關(guān)系：滿(mǎn)意度表的“學(xué)(xue)生編號” 、 “教師編號” 和用戶(hù)表的戶(hù)滿(mǎn) “編號” 聯(lián)結。

現在需要分析出學(xué)校里人員對課程的分析滿(mǎn)意度。滿(mǎn)意度的滿(mǎn)意計算方式如下：

(教師和學(xué)生對課程都滿(mǎn)意且已存在當前教務(wù)系統中的用戶(hù)) / (在學(xué)校里的人數)

【解題思路】

1.多表聯(lián)結

統計滿(mǎn)意度的前提是需要用戶(hù)在學(xué)校里，??需??要用到“用戶(hù)表”里的度的大思“是否在系統”來(lái)判斷。滿(mǎn)意度需要用到“滿(mǎn)意度表”。樣分意度用戶(hù)所以涉及到兩個(gè)表里的析用數據，就要用到《猴子 從零學(xué)會(huì )sql》里講過(guò)的戶(hù)滿(mǎn)多表聯(lián)結。

那么，使用哪種聯(lián)結呢？

統計值為滿(mǎn)意度，所以使用“滿(mǎn)意度表”為主表，進(jìn)行左聯(lián)結。

1 select * 2 from 滿(mǎn)意度表3 left join 用戶(hù)表;

如何聯(lián)??結呢？

兩個(gè)表的關(guān)系：(′?_?`)滿(mǎn)意度表的“學(xué)生編號(′?_?`)” 、 “教師編號” 和用戶(hù)表的 “編號” 聯(lián)結。所以聯(lián)結條件是：

滿(mǎn)意度表.教師編號 = 用戶(hù)表.編號 or 滿(mǎn)意度表.學(xué)生編號 = 用戶(hù)表.編號

基于上面的分析，多表聯(lián)結查詢(xún)sql語(yǔ)句如下：

1 select * 2 from 滿(mǎn)意度表3 left join 用戶(hù)表4 on (滿(mǎn)意度表.教師編號 = 用戶(hù)表.編號 or 5 滿(mǎn)意度表.學(xué)生編號 = 用戶(hù)表.編號)??;

2.查詢(xún)條件

滿(mǎn)意度等于：

(教師和學(xué)生對課程都滿(mǎn)意且已存在當前教務(wù)系統中的用戶(hù)) / (在學(xué)校里的人數)

1 where 用戶(hù)表.是否在系統 = '是'

把(ba)條件子句加入前面的多表查??詢(xún)sql里，就是下面的sql：

1 select * 2 from 滿(mǎn)意度表3 left join 用戶(hù)表4 on (滿(mǎn)意度表??.教師編號 = 用戶(hù)表.編號 or 滿(mǎn)意度(′ω｀)表.ˉ\_(ツ)_/ˉ學(xué)生編號 = 用戶(hù)表??.編號)5 where 用(yong)戶(hù)表.是(°o°)否在系統 = '是';

上面sql運行結果如下，我們發(fā)現，教師編號“02”不在學(xué)校里（對應的列“是否在系統”里的值是“否”），這是怎么回事呢？

我們重新梳理一遍邏輯。

目前的邏輯是：(教師編號 = 編號 或 學(xué)生編號 = 編號)且編號在學(xué)校中。這等價(jià)于：

(教師編號 = 編號 且 編號在學(xué)校中)或（學(xué)生編號 = 編號??，且 編號在學(xué)校中）

這里的“或”邏輯會(huì )將范圍擴大，所以且不在學(xué)校的“02”號教師也會(huì )出現在查詢(xún)結果中。

正確的做法是先從表中分別選出“在學(xué)?！崩锏膶W(xué)生和教師，然后再多表聯(lián)結。也就是：

(教師編號 = 編號 且 該編號在系統中 )并且(???)

(學(xué)生編號 = 編號 且 該編號在系統中)

對應的sql如下：

1 select * 2 from 滿(mǎn)意度表 3 left join(selecヽ(′?｀)ノt 編號 from 用戶(hù)表 where 是否在┐(′д｀)┌系統='是') as 學(xué)生4 on (滿(mǎn)意度表.學(xué)生編號 = 學(xué)生.編號)5(╯‵□′)╯ left join(sele(′?ω?`)ct 編號 from 用戶(hù)表 where 是否在系統='是') as 教師6 on (滿(mǎn)意度表.教師編號 = 教師.編號(hao));

3.統計

滿(mǎn)意度等于：

(教師和學(xué)生對課程都滿(mǎn)意且已存在當前教務(wù)系統中的用戶(hù)) / (在學(xué)校里的人數)

我??把這個(gè)公示簡(jiǎn)化為：滿(mǎn)意度=a/b

b=在學(xué)(′?_?`)校里的人數

我們只需要把a和b的值計算出來(lái)就可以啦。

統計好“是否滿(mǎn)意”列里有多少個(gè)值為“是”(?Д?)。

我們可以把“是”轉化成1，然后累計求和，對應的sql就是：

1 sum(case when 是否滿(mǎn)意='是' then 1 2 else 0 3 end)

2）計算b

b=在學(xué)校里的人數，直接用計數函數（count）就可以：count(是否滿(mǎn)意)

3）計算出滿(mǎn)意度

滿(mǎn)意度=a/b，也就是

1 sum(case when 是否滿(mǎn)意='是' then 1 else 0 end)/count(是(shi)否滿(mǎn)意) 2 as 滿(mǎn)意度

把這個(gè)計算公示加入前面sql語(yǔ)句的查詢(xún)結果里就是（下面的select子句）：

1 select sum(case when 滿(mǎn)意度表.是否滿(mǎn)意='是' then 1 else 0 end)/count(滿(mǎn)意度表.是否滿(mǎn)意) as 滿(mǎn)意度2 from 滿(mǎn)意度??表 3 left join(select 編號 from 用戶(hù)表 whヽ(′?｀)ノere 是否在系統='是') as?? 學(xué)生4 on (滿(mǎn)意度表.學(xué)生編號 = 學(xué)生.編號)5 left join(select 編號 from 用戶(hù)表 where 是否在系統='是') as 教師6 on (滿(mǎn)意度表.教師編號 = 教師.編號)

最后查詢(xún)結果是滿(mǎn)意度=0.75。

【本題考點(diǎn)】

● 考察多表查詢(xún)的應用

● 如何將業(yè)務(wù)需求轉換為sql語(yǔ)句的能力

● 聚合函數的運用

【舉一反三】

下表是一家出行公(′▽?zhuān)?)司（比如滴滴、Uber）的數據庫表。乘客通過(guò)該公司的app叫車(chē)，司機通過(guò)app接收訂單。

Users 表里存(′?｀)放的是用戶(hù)信息。每??個(gè)用戶(hù)有唯一值（Users_Id） 。Banned 表示用戶(hù)是否因為違規被禁止使用app。Role 記錄了用戶(hù)的角色，里面的值driver是司機，client是乘客，partner是合伙人(ren)。

Trips(′?_?`) 表記錄了各個(gè)出租車(chē)的行程信息。每段行程有唯一鍵（Id） 。Status 行程類(lèi)型 ‘completed’表行程??正常結束, ‘cancelled_by_driver’ 表示行程因為司機原因取消(xiao)，‘cancelled_by_c??lient’表示行程因為乘客原因???取消。

兩個(gè)表的聯(lián)結關(guān)系：Tr??ips 表（Client_Id 、 Driver_Id） 和 Users 表中 Users_Id 的聯(lián)結

寫(xiě)一段 SQL 語(yǔ)句查出非禁止用戶(hù)的取消率。

取消率的計算方式如下：(被司機或乘客取消的非禁止用戶(hù)生成的訂單數量) / (非禁止用戶(hù)??生成的訂單總數)

參考答案：

1.多表聯(lián)結，找出非禁止的用戶(hù)

1 select * 2 from tri(????)ps3 left join (select users_id from users where banned = 'no') as client4 on (trips.Client_Id = cl(′▽?zhuān)?ient.user??s_id)5 left join (select users_id from users where banned = 'no') as driver6(T_T) on (trips.Driver_Id = driver.(′▽?zhuān)?users_id);

2.按日期分組

因為要計算的是“每天”的取消率，(?????)所以要按日期分組，統計每(mei)一天的。

1 group by trips.request_at

3.計算取消率

被司┐(′д｀)┌機或乘客取消的非禁止用戶(hù)生成的訂單數量=

sum(case when status = ‘completed’ then 1 else 0 end)

非禁止用戶(hù)生成的訂單總數=count(status)

最終sql如下(xia)：

1 select sum(case when trips.status = 'completed' then 1 else 0 end) / count(trips.status) 2 from trips3 lef??t join (select users_id from users where banned = 'no') as client4 on (trips.Client_Id = client.users_id)5 left join (seleヽ(′▽?zhuān)?ノct users_id from users where banned = 'no') as driver6 on (trips.Drive(//ω//)r_Id = driver.users_id);7 group by trips.request_at;