查询
(1)可回收且低脂的产品
SELECT product_id
FROM Products
WHERE low_fats='Y' AND recyclable='Y';
很简单,最基础的sql语句。
(2)寻找用户推荐人
SELECT name
FROM Customer
WHERE referee_id != 2 OR referee_id is NULL
SQL中对于空值的处理是is NULL
或者 is NOT NULL
,对于布尔逻辑来说,null值不是true也不是false,因此null值和任何值比较结果都是UNKNOWN。为了解决这种unknown的情况,SQL使用了is NULL和is NOT NULL。
(3)大的国家
SELECT name,population,area
FROM World
WHERE population>=25000000 OR area>=3000000
即对OR的使用,可以把两个条件放在一起。
(4) 文章浏览1
SELECT distinct(author_id) as id
FROM Views
WHERE author_id=viewer_id
ORDER BY id
因为可能出现不止浏览一次的情况,因此要使用distinct进行去重,同时注意返回顺序。(逆序为DESC为逆序)
(5)无效的推文
SELECT tweet_id
FROM tweets
WHERE LENGTH(content) > 15
使用LENGTH
函数即可解决。在 MySQL 中,LENGTH
返回字符串的字节长度,而不是字符数量。因此对于多字节字符集时,如 UTF-8,用LENGTH
去处理占用多个字节的字符(例如中文),就会出现问题。因为一个中文字符占3字节。所以你可以使用CHAR_LENGTH
来处理
连接
(1)使用唯一标识码替换员工ID
SELECT unique_id, name
FROM Employees
LEFT JOIN EmployeeUNI
USING(id)
就是基础的左连接,如果有疑问可以看介绍。
左连接就是Employees根据id的进行链接EmployeeUNI,也就是一一对应。如果出现EmployeeUNI里没有能对应Employees的内容,那就只显示Employees里的内容。
(2)产品销售分析 I
SELECT product_name, year, price
FROM Sales s, Product p
WHERE s.product_id=p.product_id
不需要特殊的连接方式,直接用inner join就可以。或者我写的这种隐式链接。
这一题的本质就是将两个表合并,得到所需的信息,因此不需要额外的操作了。
(3)进店却未进行过交易的顾客
有两种做法。
# Write your MySQL query statement below
SELECT customer_id, COUNT(v.visit_id) as count_no_trans
FROM Visits v
WHERE v.visit_id NOT IN (SELECT visit_id
FROM Transactions)
GROUP BY customer_id
ORDER BY count_no_trans
首先是子查询,用子查询查出Transaction
的全部visit_id
,然后主查询的部分查找visit_id
不在Transaction
里的,就可以得到只光顾商店的客人。然后对于计算次数,可以用count聚合函数进行计算,但需要用group by
进行分组,这是因为如果不这样使用,聚合函数返回的结果是一个值,需要用分组进行分开。这样的问题在这里也出现过。
第二种做法是只使用联表查询:
select customer_id, count(customer_id) as count_no_trans
from visits
left join transactions using(visit_id)
where transaction_id is null
group by customer_id;
这里用LEFT JOIN
后,得到了每个用户的transaction
次数,但有些用户是没有交易(买东西)的,因此在where
的地方要判断transaction_id
是否为空,筛选出的结果就是未光顾的。
注意,联表查询得到的结果是一张临时表,而最基础的join(inner)
则是不会出现NULL
,对于本题是只保留1、2、5的用户,而LEFT JOIN
则可以保留多的一方的数据,得出NULL
值。
(4)上升的温度
SELECT w2.id
FROM Weather w1, Weather w2
WHERE datediff(w2.recordDate, w1.recordDate)=1 AND w2.Temperature > w1.Temperature
DATE_DIFF
是一个用于计算两个日期之间差异的函数,它返回两个日期之间的差异,以天数为单位。
本题就是后一天温度大于前一天即可,因此我们返回的应该是w2
的内容。
顺便一提,这样没有确定联表条件(例如w1.id=w2.id
)的情况查询出的内容是笛卡儿积,而WHERE的限制条件则是对其的筛选。得到的结果一定是温度上w2>w1
但日期只差一天。因此如果SELECT
的是w1
的id
,则会出现相反的结果。
(5)每台机器的进程平均运行时间
SELECT s.machine_id, ROUND(AVG(e.timestamp-s.timestamp), 3) as processing_time
FROM (SELECT machine_id, process_id, timestamp
FROM Activity
WHERE activity_type='start') as s,
(SELECT machine_id, process_id, timestamp
FROM Activity
WHERE activity_type='end') as e
WHERE s.machine_id=e.machine_id AND s.process_id=e.process_id
GROUP BY machine_id
可以把原始的表分为start表和end表,再进行联表查询,这样就可以计算时间戳的插值,进而使用AVG
计算平均值,用ROUND
保留三位小数。
(6)员工奖金
SELECT name, bonus
FROM Employee
LEFT JOIN Bonus USING(empId)
WHERE bonus<1000 OR bonus IS NULL
如果要搜出NULL
值,使用左连接,同时注意NULL
值需要单独判断。
(7) 学生们参加各科测试的次数
SELECT s.student_id, s.student_name, sub.subject_name,COUNT(e.subject_name) as attended_exams
FROM Students s
JOIN Subjects sub
LEFT JOIN Examinations e
ON e.student_id=s.student_id AND e.subject_name=sub.subject_name
GROUP BY student_id, subject_name
ORDER BY student_id
这个题最大的难点就是确保每个学生都有三个科目,即便他们没参加考试。但对于题目观察一下就可以知道,可以直接对学生表和科目表进行笛卡儿积的操作,这样就可以确保每个学生都有三个科目,再将结果进行左连接,即可确保结果是每个学生且都有三个科目。
(8)至少有5名直接下属的经理
SELECT name
FROM (SELECT COUNT(managerId) as num,managerId
FROM Employee
GROUP BY managerId
HAVING num>=5) as t, Employee e
WHERE e.id=t.managerId
从Employee
中搜索出managerID
数量大于5的managerID
,同时由managerID
分组。再和Employee
进行联表查询即可。
(9)确认率
SELECT t1.user_id, IFNULL(ROUND((t2.num/t1.num), 2), 0) as confirmation_rate
FROM(SELECT user_id, COALESCE(t.num, 0) as num
FROM(SELECT COUNT(user_id) as num,user_id
FROM Confirmations
GROUP BY user_id) as t
RIGHT JOIN Signups USING(user_id)) as t1,
(SELECT user_id, COALESCE(t.num, 0) as num
FROM(SELECT COUNT(user_id) as num,user_id
FROM Confirmations
RIGHT JOIN Signups USING(user_id)
WHERE action='confirmed'
GROUP BY user_id) as t
RIGHT JOIN Signups USING(user_id)) as t2
WHERE t1.user_id=t2.user_id
GROUP BY t2.user_id
重点之一是用IFNULL函数进行去NULL.
用两个子查询查出总数和timeout,进行除法计算。