6-14题连接 - 高频 SQL 50 题基础版

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 为null

select 
    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');   # -1

select 
    w1.id 
from 
    Weather w1, Weather w2
where
    datediff(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),获取同一机器有几个进行id

select 
    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的经理id
    from 
        Employee
    group 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;