网易校招笔试sql-牛客92、93

SQL92 商品交易(网易校招笔试真题)

描述

如下有一张商品表（goods），字段依次为：商品id、商品名、商品质量

+------+------+--------+
| id   | name | weight |
+------+------+--------+
|    1 | A1   |    100 |
|    2 | A2   |     20 |
|    3 | B3   |     29 |
|    4 | T1   |     60 |
|    5 | G2   |     33 |
|    6 | C0   |     55 |
+------+------+--------+

还有一张交易表（trans），字段依次为：交易id、商品id、这个商品购买个数

+------+----------+-------+
| id   | goods_id | count |
+------+----------+-------+
|    1 |        3 |    10 |
|    2 |        1 |    44 |
|    3 |        6 |     9 |
|    4 |        1 |     2 |
|    5 |        2 |    65 |
|    6 |        5 |    23 |
|    7 |        3 |    20 |
|    8 |        2 |    16 |
|    9 |        4 |     5 |
|   10 |        1 |     3 |
+------+----------+-------+

查找购买个数超过20,质量小于50的商品,如:

+------+------+--------+-------+
| id   | name | weight | total |
+------+------+--------+-------+
|    2 | A2   |     20 |    81 |
|    3 | B3   |     29 |    30 |
|    5 | G2   |     33 |    23 |
+------+------+--------+-------+

提交答案：

select g.id, g.name, g.weight, t.total
from goods g
JOIN
(select goods_id, sum(count) as total
from trans
group by goods_id) t
ON g.id=t.goods_id
where g.weight < 50 and t.total > 20

解析：

在子查询里按照goods_id累加购买商品的数量，命名临时表为t； 连接表goods和临时表t并在where里筛选即可。

SQL93 网易云音乐推荐(网易校招笔试真题)

描述

+---------+-------------+
| user_id | follower_id |
+---------+-------------+
|       1 |           2 |
|       1 |           4 |
|       2 |           3 |
+---------+-------------+

个人的喜欢的音乐music_likes表,第一列是用户id,第二列是喜欢的音乐id,这2列的id组成主键

+---------+----------+
| user_id | music_id |
+---------+----------+
|       1 |       17 |
|       2 |       18 |
|       2 |       19 |
|       3 |       20 |
|       4 |       17 |
+---------+----------+

这张表的第一行代表着用户id为1的喜欢music_id为17的音乐 … 这张表的第五行代表着用户id为4的喜欢music_id为17的音乐

音乐music表，第一列是音乐id，第二列是音乐name,id是主键

+----+------------+
| id | music_name |
+----+------------+
| 17 | yueyawang  |
| 18 | kong       |
| 19 | MOM        |
| 20 | Sold Out   |
+----+------------+

+------------+
| music_name |
+------------+
| kong       |
| MOM        |
+------------+

提交答案：

select music.music_name
from
(select distinct music_id
	from music_likes
		where user_id IN (select follower_id from follow where user_id=1)
    		  and
 			  music_id not IN (select music_id from music_likes where user_id=1)
) t
JOIN music ON music.id = t.music_id
order by music.id;

解析：

select follower_id from follow where user_id=1 2️⃣找出当前用户喜欢的音乐id select music_id from music_likes where user_id=1 当前临时表和music连接获取到音乐名music_name即可。

以上是两道牛客刚更新的最新sql笔试题，还是很简单的！🙂