电台节目MySQL数据库问题：多歌曲插入及超平均节目查询

嘿，我来帮你搞定这个电台节目数据库的查询问题！先从表结构优化（推荐但可选）、测试数据插入到最终查询一步步给你理清楚：

你当前的song_show表把节目基础信息和歌曲关联数据混在一起，会导致同一节目的名称、描述等信息重复存储（每首歌存一遍），既浪费空间也不利于维护。更合理的做法是拆成两张表：

• 节目主表：专门存节目本身的信息
• 节目-歌曲关联表：存节目和歌曲的对应关系

创建这两张表的SQL如下：

-- 节目主表
CREATE TABLE show_info (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name TINYTEXT NOT NULL,
    description MEDIUMTEXT NOT NULL,
    start_date DATETIME NOT NULL,
    end_date DATETIME NOT NULL,
    moderator_idmoderator INT NOT NULL
);

-- 节目-歌曲关联表（用联合主键避免同一节目重复关联同一首歌）
CREATE TABLE show_song (
    show_id INT NOT NULL,
    song_idsong INT NOT NULL,
    PRIMARY KEY (show_id, song_idsong),
    FOREIGN KEY (show_id) REFERENCES show_info(id),
    FOREIGN KEY (song_idsong) REFERENCES song(id) -- 假设你有专门的song表存歌曲信息
);

如果你不想改动现有表结构，也可以继续用原来的song_show表，只是插入数据时要注意同一节目ID对应多条记录（每首歌一条）。

基于优化后的表结构插入 #

先插节目基础数据，再插节目和歌曲的关联数据：

-- 插入5个节目
INSERT INTO show_info (name, description, start_date, end_date, moderator_idmoderator)
VALUES
('深夜MTV怀旧档', '80-90年代经典MTV专场', '2024-06-01 22:00:00', '2024-06-02 00:00:00', 1),
('深夜MTV欧美档', '当月欧美流行新歌速递', '2024-06-02 22:00:00', '2024-06-03 00:30:00', 2),
('深夜MTV华语档', '华语金曲精选合集', '2024-06-03 22:00:00', '2024-06-04 00:00:00', 1),
('深夜MTV小众档', '独立音乐人原创作品', '2024-06-04 22:30:00', '2024-06-05 01:00:00', 3),
('深夜MTV原声档', '热门电影原声特辑', '2024-06-05 22:00:00', '2024-06-06 00:45:00', 2);

-- 给每个节目关联不同数量的歌曲
-- 节目1：2首歌
INSERT INTO show_song (show_id, song_idsong) VALUES (1, 101), (1, 102);
-- 节目2：5首歌
INSERT INTO show_song (show_id, song_idsong) VALUES (2, 201), (2, 202), (2, 203), (2, 204), (2, 205);
-- 节目3：3首歌
INSERT INTO show_song (show_id, song_idsong) VALUES (3, 301), (3, 302), (3, 303);
-- 节目4：1首歌
INSERT INTO show_song (show_id, song_idsong) VALUES (4, 401);
-- 节目5：4首歌
INSERT INTO show_song (show_id, song_idsong) VALUES (5, 501), (5, 502), (5, 503), (5, 504);

基于原表song_show插入 #

如果用你原来的表，需要给同一节目ID重复插入多条记录（每首歌对应一条）：

INSERT INTO song_show (id, name, description, start_date, end_date, song_idsong, moderator_idmoderator)
VALUES
-- 节目1（2首歌）
(1, '深夜MTV怀旧档', '80-90年代经典MTV专场', '2024-06-01 22:00:00', '2024-06-02 00:00:00', 101, 1),
(1, '深夜MTV怀旧档', '80-90年代经典MTV专场', '2024-06-01 22:00:00', '2024-06-02 00:00:00', 102, 1),
-- 节目2（5首歌）
(2, '深夜MTV欧美档', '当月欧美流行新歌速递', '2024-06-02 22:00:00', '2024-06-03 00:30:00', 201, 2),
(2, '深夜MTV欧美档', '当月欧美流行新歌速递', '2024-06-02 22:00:00', '2024-06-03 00:30:00', 202, 2),
(2, '深夜MTV欧美档', '当月欧美流行新歌速递', '2024-06-02 22:00:00', '2024-06-03 00:30:00', 203, 2),
(2, '深夜MTV欧美档', '当月欧美流行新歌速递', '2024-06-02 22:00:00', '2024-06-03 00:30:00', 204, 2),
(2, '深夜MTV欧美档', '当月欧美流行新歌速递', '2024-06-02 22:00:00', '2024-06-03 00:30:00', 205, 2),
-- 其他节目以此类推，按需要的歌曲数量插入对应条数

基于优化后的表结构查询 #

这个查询会先统计每个节目的歌曲数，计算所有节目的平均歌曲数，最后筛选出高于平均值的节目：

SELECT 
    si.id AS show_id,
    si.name AS show_name,
    COUNT(ss.song_idsong) AS song_count
FROM 
    show_info si
JOIN 
    show_song ss ON si.id = ss.show_id
GROUP BY 
    si.id, si.name
HAVING 
    song_count > (
        -- 内层子查询先算每个节目的歌曲数，再求平均值
        SELECT AVG(inner_count)
        FROM (
            SELECT COUNT(song_idsong) AS inner_count
            FROM show_song
            GROUP BY show_id
        ) AS avg_calculation
    );

基于原表song_show查询 #

逻辑和上面一致，只是直接从原表分组统计：

SELECT 
    id AS show_id,
    name AS show_name,
    COUNT(song_idsong) AS song_count
FROM 
    song_show
GROUP BY 
    id, name
HAVING 
    song_count > (
        SELECT AVG(inner_count)
        FROM (
            SELECT COUNT(song_idsong) AS inner_count
            FROM song_show
            GROUP BY id
        ) AS avg_calculation
    );

简单解释下这个查询的逻辑：

1. 最内层的子查询先按节目ID分组，统计每个节目的歌曲数量
2. 中间层用AVG()计算这些数量的平均值
3. 外层查询同样统计每个节目的歌曲数，并用HAVING过滤出数量大于平均值的节目

这样就能得到你想要的结果啦！

内容的提问来源于stack exchange，提问作者Maki