牛客网数据库SQL实战（五）

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

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 t.total > 20 AND g.weight < 50
ORDER BY g.id ASC

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

SELECT DISTINCT m.music_name
FROM follow f JOIN music_likes l ON f.follower_id = l.user_id JOIN music m ON l.music_id = m.id
WHERE f.user_id = '1' AND l.music_id NOT IN (
    SELECT music_id
    FROM music_likes
    WHERE user_id = '1'
)
ORDER BY l.music_id ASC

SQL94 今天的刷题量(一)

SELECT name, cnt
FROM (
    SELECT subject_id, name, COUNT(1) AS cnt
    FROM submission JOIN subject ON submission.subject_id = subject.id
    WHERE create_time = CURDATE()
    GROUP BY subject_id, name
    ORDER BY cnt DESC, subject_id
) tmp