mysql常用语句备份

case… when…

group_concat

--- 缺集节目列表及分集
select case when mtype=0 then '电影'
when mtype=1 then '电视剧'
when mtype=2 then '短视频'
when mtype=3 then '预告片'
when mtype=4 then '花絮'
when mtype=5 then '混合类型'
when mtype=6 then '动漫'
when mtype=7 then '综艺'
when mtype=8 then '少儿'
when mtype=9 then '生活'
when mtype=10 then '纪录片'
when mtype=11 then '3D影片'
when mtype=12 then '电视回看'
when mtype=13 then '超高清'
when mtype=14 then '体育'
when mtype=15 then '音乐MTV'
when mtype=16 then '娱乐频道'
when mtype=17 then '时效热点'
when mtype=18 then '电视回看(真)'
when mtype=19 then '专题(B2B项目专用)'
when mtype=20 then '世界杯'
when mtype=21 then '购物'
when mtype=22 then '卡拉OK'
end as mtype1,mtype,rm.mid,cnname,GROUP_CONCAT(rs.sid ORDER BY rs.sid) as sid
 from voole_vrm.vrm_sp_authorlist rs
INNER JOIN voole_global.res_movieinfo rm ON rs.mid = rm.mid
where ispid='66660011' and mime='mp4' and rs.mid in (
    SELECT mid from voole_global.res_movieextend where ispid='66660011' and injectstatus=2 
) and fid not in (
    select fid from voole_global.res_mediadown where downstatus in (104,113,213)
)
GROUP BY rm.mid order by mtype,rm.mid