实验10 SQL互联网业务查询-1

第1关 查找购买个数超过20,重量小于50的商品，按照商品id升序排序

USE mygoods;
########## Begin ##########
SELECT G.id,SUM(T.count) total
FROM goods G
JOIN trans T USING(id)
WHERE weight < 50
GROUP BY T.id
HAVING SUM(T.count)>20
ORDER BY G.id;
########## End ##########

第2关 查询向follow表中user_id = 1 的用户，推荐其关注的人喜欢的音乐

USE mymusic;

########## Begin ##########
SELECT DISTINCT music_name
FROM music
WHERE id IN(
    SELECT music_id
    FROM music_likes mi,follow f
    WHERE mi.user_id = f.follower_id AND f.user_id = 1
        AND music_id NOT IN(
        SELECT music_id
        FROM music_likes mi
        WHERE user_id = 1
    )
)
ORDER BY music_name
########## End ##########

第3关 查询向follow表中user_id用户，推荐其关注的人喜欢的音乐

USE mymusic;
########## Begin ##########
SELECT DISTINCT f.user_id,music_name
FROM music_likes mi
RIGHT JOIN follow f ON f.follower_id = mi.user_id
LEFT JOIN music m ON mi.music_id = m.id
WHERE music_id NOT IN(
    SELECT music_id
    FROM (
        SELECT Tablea.*
        FROM (
            SELECT
            mi1.*,
            f1.follower_id
            FROM follow f1,music_likes mi1
            WHERE f1.user_id = mi1.user_id
        ) Tablea,music_likes mi2
        WHERE Tablea.follower_id = mi2.user_id AND Tablea.music_id = mi2.music_id
    ) Tableb
)
ORDER BY user_id,music_name

########## End ##########

第4关 查询用户日活数及支付金额

USE myusers;
########## Begin ##########
SELECT
   lr.dt,
   COUNT(DISTINCT lr.user_id) dau,
   SUM(nu.is_new) dau_new,
   SUM(up.pay_money) total_pay,
   SUM(IF(nu.is_new=1,up.pay_money,NULL)) total_pay_new
FROM (SELECT DISTINCT user_id,dt FROM login_record) lr
LEFT JOIN new_user nu ON lr.user_id = nu.user_id
LEFT JOIN user_pay up ON lr.user_id = up.user_id AND lr.dt = up.dt

GROUP BY lr.dt
HAVING SUM(up.pay_money) IS NOT NULL
########## End ##########

第5关 查询各工程号最大的按零件号合计供应量以及该零件号，并先按工程号升序，再按零件号升序排序。

USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT JNO,PNO,SUM_QTY maxsum
FROM (
    SELECT JNO,PNO,SUM(QTY) SUM_QTY
    FROM SPJ
    GROUP BY JNO,PNO
    ORDER BY JNO,PNO
) A
WHERE SUM_QTY =(
    SELECT MAX(SUM_QTY) maxsum
    FROM(
        SELECT JNO,PNO,SUM(QTY) SUM_QTY
            FROM SPJ
            GROUP BY JNO,PNO
            ORDER BY JNO,PNO) B
    WHERE A.JNO = B.JNO
)

########## End ##########