1. 相關知識點

• left join

  • 以左表為基準，返回左表中所有的行，同時返回右表中與左表匹配的行。
  • 如果右表中沒有匹配的行，則用NULL填充。

• join和left join的區別

  • 如果是join則右側的數據有的就插，沒的就啥也不干，交白卷，也不留null
  • 但是left join讓右側數據在沒有對應數據時補上了null

• CROSS JOIN產生了一個結果集，該結果集是兩個關聯表的行的乘積

  • 2行表,與3行表使用cross join,得到2*3=6行數據

• 相關函數

  函數	例子	含義
  DATEDIFF(前，后)	DATEDIFF(‘2007-12-31’,‘2007-12-30’); # 1	兩個日期的差，前-后
  sum()	sum(salary）	根據分組求和
  if (判斷條件，符合賦值，不符合賦值)	if (salary>1000,1,0)	根據if條件語句取值
  sum(if( ))	sum( if (salary>1000,1,0))	根據if條件語句賦值再根據分組求和
  avg(if( ))	avg( if (salary>1000,1,0))	根據if條件語句賦值再根據分組求均值
  round(,n)	round(salary,3)	保留n位小數

2. 例子

2.6. 使用唯一標識碼替換員工ID

select unique_id,name 
from Employees e left join EmployeeUNI e1 
on e.id=e1.id;

2.7- 產品銷售分析 I

select product_name,year,price
from Sales left join Product 
on Sales.product_id = Product.product_id;

2.8 - 進店卻未進行過交易的顧客

-- 顧客可能光顧了購物中心但沒有進行交易,一個顧客可能光顧多次，需用顧客id分組
-- 使用COUNT(*)可以輸出GROUP BY后每個分組中的數據數量
-- 左連表，右表沒有的數據賦值為null,即沒有交易的transaction_id 為nullselect v.customer_id,count(*) as count_no_trans
from Visits v left join Transactions t on v.visit_id=t.visit_id
where t.transaction_id is null group by v.customer_id;

2.9 - 上升的溫度

-- 找出與之前（昨天的）日期相比溫度更高的所有日期的 id
-- DATEDIFF('2007-12-31','2007-12-30');   # 1
-- DATEDIFF('2010-12-30','2010-12-31');   # -1select w1.id 
from Weather w1, Weather w2
wheredatediff(w1.recordDate,w2.recordDate)=1 and w1.temperature>w2.temperature;

2.10 - 每臺機器的進程平均運行時間

-- sum(if(activity_type = 'end',timestamp ,-timestamp ))
-- 如果activity_type為“end”,值為timestamp，為“start” 為-timestamp，所有數相加=end-start
-- count(distinct process_id),獲取同一機器有幾個進行idselect machine_id , round(sum(if(activity_type = 'end',timestamp ,-timestamp ))/count(distinct process_id),3) as processing_time 
from Activity 
group by machine_id;-- AVG(IF(activity_type = 'start', -timestamp, timestamp))
-- 如果activity_type為“end”,值為timestamp，為“start” 為-timestamp，所有數相加=end-start
-- 將所有數求平均，avg(1,2,3,4)/4，多除了2倍SELECT machine_id, ROUND(AVG(IF(activity_type = 'start', -timestamp, timestamp))*2,3) AS processing_time 
FROM Activity 
GROUP BY machine_id;

2.11- 員工獎金

-- join和left join的區別
-- 如果是join則右側的數據有的就插，沒的就啥也不干，交白卷，也不留null
-- 但是left join讓右側數據在沒有對應數據時補上了null
select e.name,b.bonus
from Employee e left join bonus b
on e.empId=b.empId
where b.bonus <1000 or b.bonus is null;

2.12-學生們參加各科測試的次數

-- 學生表中，id是唯一的，將他作為主表
--  CROSS JOIN產生了一個結果集，該結果集是兩個關聯表的行的乘積
-- 2行表,與3行表使用cross join,得到2*3=6行數據
select st.student_id, st.student_name,su.subject_name,count(e.subject_name) AS attended_exams 
from Students st 
cross join Subjects su 
left join Examinations e 
on e.student_id=st.student_id and e.subject_name=su.subject_name
group by st.student_id, st.student_name,su.subject_name 
order by st.student_id,st.student_name;

2.13-至少有5名直接下屬的經理

select name
from Employee 
where id in (select managerId  -- 查找大于5的經理idfrom Employeegroup by managerId  -- 根據id分組having count(*)>=5); -- 根據分組的數據進行求個數

2.14 - 確認率

-- s為注冊表，有所有用戶的信息，即為主表
select s.user_id,round(sum(if(action="confirmed",1,0))/count(s.user_id),2) confirmation_rate 
from Signups s 
left join Confirmations c 
on s.user_id =c.user_id 
group by s.user_id;