高频 SQL 50 题（基础版）连接部门

1、使用唯一标识码更换员工ID

1. # Write your MySQL query statement below
2. SELECT
3.     b.unique_id, a.name
4. FROM
5.     Employees as a
6. LEFT JOIN
7.     EmployeeUNI as b
8. ON
9.     a.id = b.id;

复制代码

2、产品贩卖分析 I

1. # Write your MySQL query statement below
2. SELECT
3.     p.product_name, s.year, s.price
4. FROM
5.     Sales s
6. JOIN
7.     Product p
8. ON
9.     s.product_id = p.product_id

复制代码

总结

• INNER JOIN：仅返回匹配的记录。
  
• LEFT JOIN：返回左表的所有记录，即使右表没有匹配的记录。
  
• RIGHT JOIN：返回右表的所有记录，即使左表没有匹配的记录。
  
• FULL JOIN：返回左表和右表的所有记录，没匹配的部门用 NULL 添补。
  
• CROSS JOIN：返回两张表的笛卡尔积，生成所有大概的组合。
  
• SELF JOIN：将表与自身进行连接，常用于表示层级关系。
  
• NATURAL JOIN：自动根据同名列进行连接。
  

在MySQL中，JOIN操作默认使用的是INNER JOIN。INNER JOIN是MySQL默认的JOIN类型。它返回两个表中符合条件的行。INNER JOIN使用ON关键字来指定连接条件，将两个表中符合条件的记录合并在一起，生成一个新的结果集。

3、进店却未进行过交易的顾客

1. # Write your MySQL query statement below
2. SELECT
3.     v.customer_id,count(v.customer_id)count_no_trans
4. FROM
5.     Visits v
6. LEFT JOIN
7.     Transactions t
8. ON
9.     v.visit_id = t.visit_id
10. WHERE
11.     transaction_id is NULL
12. GROUP BY
13.     v.customer_id

复制代码

4、上升的温度

1. SELECT
2.     a.id
3. FROM
4.     Weather as a
5. CROSS JOIN
6.     Weather as b
7. WHERE
8.     datediff(a.recordDate,b.recordDate) = 1
9. AND
10.     a.Temperature >b.Temperature

复制代码

 
5、每台机器的历程平均运行时间

1. # Write your MySQL query statement below
2. SELECT
3.     machine_id, round(sum(if(activity_type = 'start', -timestamp, timestamp)) / count(*)*2, 3) as processing_time
4. FROM
5.     Activity
6. GROUP BY
7.     machine_id

复制代码

6、员工奖金

1. # Write your MySQL query statement below
2. SELECT
3.     e.name, b.bonus
4. FROM
5.     Employee e
6. LEFT JOIN
7.     Bonus b
8. ON
9.     e.empId = b.empId
10. WHERE
11.     b.bonus is NULL OR b.bonus < 1000

复制代码

 
7、门生们参加各科测试的次数

1. SELECT
2.     s.student_id, s.student_name, sub.subject_name, IFNULL(grouped.attended_exams, 0) AS attended_exams
3. FROM
4.     Students s
5. CROSS JOIN
6.     Subjects sub
7. LEFT JOIN (
8.     SELECT student_id, subject_name, COUNT(*) AS attended_exams
9.     FROM Examinations
10.     GROUP BY student_id, subject_name
11. ) grouped
12. ON s.student_id = grouped.student_id AND sub.subject_name = grouped.subject_name
13. ORDER BY s.student_id, sub.subject_name;

复制代码

8、至少有5名直接下属的司理

1. # Write your MySQL query statement below
2. SELECT
3.     b.name
4. FROM
5.     Employee a
6. LEFT JOIN
7.     Employee b
8. ON
9.     a.managerId = b.id
10. GROUP BY
11.     a.managerId
12. HAVING
13.     COUNT(b.id)>=5  

复制代码

9、确认率

1. # Write your MySQL query statement below
2. SELECT
3.     T1.user_id,round(count(if(T2.action = "confirmed",true,null)) / count(*),2) AS confirmation_rate
4. FROM
5.     Signups as T1
6. JOIN
7.     confirmations AS T2
8. ON
9.     T1.user_id = T2.user_id
10. GROUP BY
11.     T1.user_id;

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。