统计每天和每周播放量按用户排序

1、昨日播放量按用户排序

$yesterday = date('Y-m-d',strtotime('-1 days'));
$field = "v_user.*,v_play_record.num, case `date` WHEN '".$yesterday."' THEN date ELSE '1970-01-01' END as date1";
$join = "LEFT JOIN v_play_record ON v_user.id = v_play_record.uid2 and date='".$yesterday."'";
$order = 'date1 desc,num desc, v_user.id DESC';
$data = M("User")->join($join)->field($field)->order($order)->page($page,10)->select();

最终的sql为

$sql = "select v_user.*,v_play_record.num, case `date` WHEN '2018-04-19' THEN date ELSE '1970-01-01' END as date1 FROM v_user LEFT JOIN v_play_record ON v_user.id = v_play_record.uid2 and date='2018-04-19' ORDER BY date1 desc, num desc, v_user.id DESC";
$data = M('User')->query($sql);

2、上周播放量按用户排序

$last_week = date('YW',strtotime('-7 days'));
$field = "v_user.*,sum(v_play_record.num), case `week` WHEN '".$last_week."' THEN week ELSE '201801'END as week1 ";
$join = "LEFT JOIN v_play_record ON v_user.id = v_play_record.uid2 and week='".$last_week."'";
$order = 'week1 desc,sum(v_play_record.num) desc,v_user.id desc';
$group = 'v_user.id';
$data = M("User")->join($join)->field($field)->order($order)->group($group)->page($page,10)->select();

最终的sql为

$sql = "select v_user.id,sum(v_play_record.num), case `week` WHEN '201815' THEN week ELSE '201801'END as week1 FROM v_user LEFT JOIN v_play_record ON v_user.id = v_play_record.uid2 and week='201815' GROUP BY v_user.id ORDER BY week1 desc,sum(v_play_record.num) desc,v_user.id desc";
$data = M('User')->query($sql);

case when的其他通俗用法

$data = M('Article')
    ->field("id,pre_img,case when (start_time<='".$time."' and end_time>='".$time."') then 1 else 0 end as status")
    ->where($where)
    ->order('status desc,id desc')
    ->page($p,5)
    ->select();