#封装所有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;