241024-tingshulv/lesson.sql

#封装所有1,2,3级分类到视图中 包含：分类ID，分类名称 其他辅助字段
create or replace view base_category_view as
select bc3.id,
       bc1.id   category1_id,
       bc1.name category1_name,
       bc2.id   category2_id,
       bc2.name category2_name,
       bc3.id   category3_id,
       bc3.name category3_name,
       bc3.create_time,
       bc3.update_time,
       bc3.is_deleted
from base_category1 bc1
         inner join base_category2 bc2 on bc2.category1_id = bc1.id and bc2.is_deleted = 0
         inner join base_category3 bc3 on bc3.category2_id = bc2.id and bc3.is_deleted = 0
where bc1.is_deleted = 0;

#执行计划
explain select bc3.id,
               bc1.id   category1_id,
               bc1.name category1_name,
               bc2.id   category2_id,
               bc2.name category2_name,
               bc3.id   category3_id,
               bc3.name category3_name,
               bc3.create_time,
               bc3.update_time,
               bc3.is_deleted
        from base_category1 bc1
                 inner join base_category2 bc2 on bc2.category1_id = bc1.id and bc2.is_deleted = 0
                 inner join base_category3 bc3 on bc3.category2_id = bc2.id and bc3.is_deleted = 0
        where bc1.is_deleted = 0;


#需求：根据1级分类ID查询该分类下关联标签及标签值列表
select
    ba.id,
    ba.attribute_name,
    bav.id base_attribute_value_id,
    bav.attribute_id,
    bav.value_name
from base_attribute ba inner join base_attribute_value bav
                                  on bav.attribute_id = ba.id and bav.is_deleted = 0
where ba.category1_id = 2 and ba.is_deleted = 0;

explain select
            ba.id,
            ba.attribute_name,
            bav.id base_attribute_value_id,
            bav.attribute_id,
            bav.value_name
        from base_attribute ba inner join base_attribute_value bav
                                          on bav.attribute_id = ba.id and bav.is_deleted = 0
        where ba.category1_id = 2 and ba.is_deleted = 0

#需求：根据查询条件（用户ID、关键字、状态）查询专辑列表，包含：专辑信息以及专辑统计信息

#第一步：查询业务数据来源专辑表跟专辑统计表 关联条件：统计表专辑ID跟专辑表主键  过滤条件：用户ID、关键字、状态 封装AlbumListVo
select
    ai.id album_id,
    ai.album_title,
    ai.cover_url,
    ai.include_track_count,
    ai.is_finished,
    ai.status,
    stat.stat_type,
    stat.stat_num
from album_info ai inner join album_stat stat on stat.album_id = ai.id;


#第二步，上面得到统计是4条记录，需要封装成1条记录，封装到AlbumListVo中，封装字段：播放量、点赞量、收藏量、评论量  行转为列
#2.1 尝试根据专辑ID分组 问题：违背SQL严格模式语法规则only_full_group_by，select中只能分组字段、聚合函数 由于查询列中包含多方统计类型，统计数值

select
    ai.id album_id,
    ai.album_title,
    ai.cover_url,
    ai.include_track_count,
    ai.is_finished,
    ai.status,
    stat.stat_type,
    stat.stat_num
from album_info ai inner join album_stat stat on stat.album_id = ai.id
group by ai.id;


#2.2 无法再Group by增加分组字段，从Select作为切入点  Select部分可以使用聚合函数
# 采用判断函数 if(条件，满足结果，不满足结果)
select if(1=2, 'true', 'false');

# 以其中某条统计数值为例 得到统计类型及数值 转为列
select * from album_stat where album_id = 2;
select if(stat_type='0401', stat_num, 0) play_stat_num,if(stat_type='0402', stat_num, 0) subscribeStatNum  from album_stat where album_id = 2;

# 完善SQL 对统计每行记录进行判断
explain select
            ai.id album_id,
            ai.album_title,
            ai.cover_url,
            ai.include_track_count,
            ai.is_finished,
            ai.status,
            max(if(stat.stat_type='0401', stat_num, 0)) playStatNum,
            max(if(stat.stat_type='0402', stat_num, 0)) subscribeStatNum,
            sum(if(stat.stat_type='0403', stat_num, 0)) buyStatNum,
            max(if(stat.stat_type='0404', stat_num, 0)) commentStatNum
        from album_info ai inner join album_stat stat on stat.album_id = ai.id
        group by ai.id;


#第三步：增加过滤条件，排序
explain select
            ai.id album_id,
            ai.album_title,
            ai.cover_url,
            ai.include_track_count,
            ai.is_finished,
            ai.status,
            max(if(stat.stat_type='0401', stat_num, 0)) playStatNum,
            max(if(stat.stat_type='0402', stat_num, 0)) subscribeStatNum,
            sum(if(stat.stat_type='0403', stat_num, 0)) buyStatNum,
            max(if(stat.stat_type='0404', stat_num, 0)) commentStatNum
        from album_info ai inner join album_stat stat on stat.album_id = ai.id
        where ai.user_id = ?
          and ai.album_title like concat('%','世界','%')
          and ai.status = ?
          and ai.is_deleted = 0
        group by ai.id
        order by ai.id desc;


#需求：条件分页查询声音列表，包含声音统计信息 封装到TrackListVo对象中

#第一步：关联查询声音表跟统计表 关联条件：声音表ID跟统计表声音ID
select
    ti.id track_id,
    ti.track_title,
    ti.cover_url,
    ti.media_duration,
    ti.status,
    stat.stat_type,
    stat.stat_num
from track_info ti inner join track_stat stat on stat.track_id = ti.id;

#第二步：先根据声音ID分组，借助if函数获取某种统计类型对应数值（4个数值） + max函数获取有效统计数值
select
    ti.id track_id,
    ti.track_title,
    ti.cover_url,
    ti.media_duration,
    ti.status,
    max(if(stat_type='0701', stat_num, 0)) playStatNum,
    max(if(stat_type='0702', stat_num, 0)) collectStatNum,
    max(if(stat_type='0703', stat_num, 0)) praiseStatNum,
    max(if(stat_type='0704', stat_num, 0)) commentStatNum
from track_info ti inner join track_stat stat on stat.track_id = ti.id
group by ti.id
order by ti.id desc;


#第三步过滤条件
explain select
            ti.id track_id,
            ti.track_title,
            ti.cover_url,
            ti.media_duration,
            ti.status,
            max(if(stat_type='0701', stat_num, 0)) playStatNum,
            max(if(stat_type='0702', stat_num, 0)) collectStatNum,
            max(if(stat_type='0703', stat_num, 0)) praiseStatNum,
            max(if(stat_type='0704', stat_num, 0)) commentStatNum
        from track_info ti inner join track_stat stat on stat.track_id = ti.id and stat.is_deleted = 0
        where
                ti.user_id = 1
          and ti.status = '0501'
          and ti.track_title like concat('%','世界','%')
          and ti.is_deleted = 0
        group by ti.id
        order by ti.id desc;



# 根据专辑ID查询专辑统计信息
select * from album_stat where album_id = 1;

explain select
            album_id,
            max(if(stat_type='0401', stat_num, 0)) playStatNum,
            max(if(stat_type='0402', stat_num, 0)) subscribeStatNum,
            sum(if(stat_type='0403', stat_num, 0)) buyStatNum,
            max(if(stat_type='0404', stat_num, 0)) commentStatNum
        from album_stat where album_id = 1 and is_deleted = 0;



#需求：根据专辑ID，分页获取专辑下声音列表 声音统计信息

explain select
            ti.id track_id,
            ti.track_title,
            ti.media_duration,
            ti.order_num,
            ti.create_time,
            max(if(stat_type='0701', stat_num, 0)) playStatNum,
            max(if(stat_type='0702', stat_num, 0)) collectStatNum,
            max(if(stat_type='0703', stat_num, 0)) praiseStatNum,
            max(if(stat_type='0704', stat_num, 0)) commentStatNum
        from track_info ti inner join track_stat ts on ts.track_id = ti.id and ts.is_deleted = 0
        where ti.album_id = 1 and ti.status = '0501' and ti.is_deleted = 0
        group by ti.id
        order by ti.id asc;