在做选盘网项目中遇到的需求：
$res = $project->query("SELECT * FROM (SELECT cr.object_id,sum(hits) total_like FROM cmf_ranking cr WHERE $order(date)=$order(now()) GROUP BY cr.object_id UNION SELECT cp.id,cp.redundance total_like FROM cmf_project cp WHERE cp.is_down = 1 and cp.status = 1 AND cp.stop_time > unix_timestamp()) tmp GROUP BY object_id ORDER BY total_like DESC");
这段sql语句用到了union all和group by，具体用法百度
// 最热项目列表页
public function projecthot()
{
$keywords = I('keywords', '');
$where['p_name'] = array('like', '%' . $keywords . '%');
$order = 'day';
if (I('order')) {
$order = I('order');
}
$project = M('Project');
$where['is_down'] = array('eq', 1); // 未下架的
//增加时效
$where['stop_time'] = array('EGT', time());
$where['status'] = array('eq', 1);
// 查询侧边栏的最新和最热十条记录
$newdata = $this->getProjectNew();
$hotdata = $this->getProjectHot();
if ($order != 'total' && empty($keywords)) {
// $now = time();
// $ranking = $project->query("select *,sum(hits) total_like from cmf_ranking cr,cmf_project cp where cp.id=cr.object_id and $order(date)=$order(now()) and cp.is_down = 1 and cp.stop_time > $now and cp.status = 1 group by object_id order by sum(hits) desc limit 0,10");
$res = $project->query("SELECT * FROM (SELECT cr.object_id,sum(hits) total_like FROM cmf_ranking cr WHERE $order(date)=$order(now()) GROUP BY cr.object_id UNION SELECT cp.id,cp.redundance total_like FROM cmf_project cp WHERE cp.is_down = 1 and cp.status = 1 AND cp.stop_time > unix_timestamp()) tmp GROUP BY object_id ORDER BY total_like DESC");
// $ranking = $project->query("select cp.*,SUM(hits) total_like from cmf_project cp left JOIN cmf_ranking cr on cp.id = cr.object_id WHERE cp.is_down = 1 and cp.status = 1 GROUP BY cp.id ORDER BY total_like desc");
// $count = count($ranking);
// $Page = new \Think\Page($count, 10);
// $data1 = $project->query("select cp.*,SUM(hits) total_like from cmf_project cp left JOIN cmf_ranking cr on cp.id = cr.object_id WHERE $order(date)=$order(now()) and cp.is_down = 1 and cp.status = 1 GROUP BY cp.id ORDER BY total_like desc limit $Page->firstRow, $Page->listRows");
// $data2 = $project->query("select cp.*,SUM(hits) total_like from cmf_project cp left JOIN cmf_ranking cr on cp.id = cr.object_id WHERE cp.is_down = 1 and cp.status = 1 GROUP BY cp.id ORDER BY total_like desc limit $Page->firstRow, $Page->listRows");
// for ($i = 0; $i < $count; $i++) {
// $data['pro'][] = array_merge($data2[$i], $data1[$i]);
// }
// $data['page'] = $Page->show();
// foreach ($ranking as $k => $v) {
// $key_id[] = $v['id'];
// }
// if ($key_id) {
// $num = 10 - count($ranking);
// $where['id'] = array('not in', $key_id);
// $projecting = M('Project')->where($where)->limit(0, $num)->select();
// $data['pro'] = array_merge_recursive($ranking, $projecting);
// } else {
// $data['pro'] = $project->where($where)->order('post_like desc')->limit(0, 10)->select();
// }
// 取出点赞的数量和价格
// $option = M('Options')->where("option_name='site_options'")->find();
// $option01 = json_decode($option['option_value'], true);
// $site_like_price = json_decode(htmlspecialchars_decode($option01['site_like_price']), true);
// foreach ($site_like_price as $k => $v) {
// $like_price[$k] = '赞' . $k . '个(' . $v . '元)';
// }
// $this->assign('data', $data);
// $this->assign('order', $order);
// $this->assign('like_price', $like_price);
// $this->display();die;
} else {
$res = $project->query("SELECT * FROM (SELECT cr.object_id,sum(hits) total_like FROM cmf_ranking cr GROUP BY cr.object_id UNION SELECT cp.id,cp.redundance total_like FROM cmf_project cp WHERE cp.is_down = 1 and cp.status = 1 AND cp.stop_time > unix_timestamp()) tmp GROUP BY object_id ORDER BY total_like DESC");
}
$count = count($res);
$Page = new \Think\Page($count, 10);
foreach ($res as $k => $v) {
$ids[] = $v['object_id'];
}
$map['id'] = array('in', $ids);
$res2 = $project->where($map)->limit($Page->firstRow . ',' . $Page->listRows)->select();
foreach ($res2 as $k1 => $v1) {
foreach ($res as $k2 => $v2) {
if ($v1['id'] == $v2['object_id']) {
$res2[$k1]['total_like'] = $res[$k2]['total_like'];
}
}
}
//二维数组排序
$sort = array(
'direction' => 'SORT_DESC', //排序顺序标志 SORT_DESC 降序；SORT_ASC 升序
'field' => 'total_like', //排序字段
);
$arrSort = array();
foreach ($res2 as $uniqid => $row) {
foreach ($row as $key => $value) {
$arrSort[$key][$uniqid] = $value;
}
}
if ($sort['direction']) {
array_multisort($arrSort[$sort['field']], constant($sort['direction']), $res2);
}
$data['pro'] = $res2;
$data['page'] = $Page->show();
// $count = $project->where($where)->count();
// $Page = new \Think\Page($count, 10);
// $data['pro'] = $project->where($where)->order('post_like desc')->limit($Page->firstRow . ',' . $Page->listRows)->select();
// $data['page'] = $Page->show();
// 取出点赞的数量和价格
$option = M('Options')->where("option_name='site_options'")->find();
$option01 = json_decode($option['option_value'], true);
$site_like_price = json_decode(htmlspecialchars_decode($option01['site_like_price']), true);
foreach ($site_like_price as $k => $v) {
$like_price[$k] = '赞' . $k . '个(' . $v . '元)';
}
$this->assign(array(
'data' => $data,
'newdata' => $newdata,
'hotdata' => $hotdata,
'order' => $order,
'like_price' => $like_price,
));
$this->display();
}
以上就是整个方法代码
MYSQL语句的调整：
SELECT pro.*,IFNULL(cr.total_hit,0) AS total_like FROM cmf_project AS pro LEFT JOIN (SELECT object_id, SUM(hits) AS total_hit FROM cmf_ranking WHERE MONTH(date)=MONTH(now()) GROUP BY object_id) AS cr ON pro.id=cr.object_id HAVING pro.is_down = 1 AND pro.status = 1 AND pro.stop_time > unix_timestamp() ORDER BY total_like DESC
以下是整个文件代码：
// 最热项目列表页
public function projecthot()
{
$nowPage = I('p', 1);
$firstRow = 20 * ($nowPage - 1);
$keywords = I('keywords', '');
$where['p_name'] = array('like', '%' . $keywords . '%');
$order = 'day';
if (I('order')) {
$order = I('order');
}
$project = M('Project');
$where['is_down'] = array('eq', 1); // 未下架的
//增加时效
$where['stop_time'] = array('EGT', time());
$where['status'] = array('eq', 1);
// 查询侧边栏的最新和最热十条记录
$newdata = $this->getProjectNew();
$hotdata = $this->getProjectHot();
// $num = $project->query("SELECT * FROM (SELECT cr.object_id,sum(hits) total_like FROM cmf_ranking cr GROUP BY cr.object_id UNION SELECT cp.id,cp.redundance total_like FROM cmf_project cp WHERE cp.is_down = 1 and cp.status = 1 AND cp.stop_time > unix_timestamp()) tmp LEFT JOIN cmf_project cp on cp.id=tmp.object_id GROUP BY object_id ORDER BY total_like DESC");
$num = $project->query("SELECT pro.*,IFNULL(cr.total_hit,0) AS total_like FROM cmf_project AS pro LEFT JOIN (SELECT object_id, SUM(hits) AS total_hit FROM cmf_ranking GROUP BY object_id) AS cr ON pro.id=cr.object_id HAVING pro.is_down = 1 AND pro.status = 1 AND pro.stop_time > unix_timestamp() ORDER BY total_like DESC");
$count = count($num);
if ($order != 'total') {
// $now = time();
// $ranking = $project->query("select *,sum(hits) total_like from cmf_ranking cr,cmf_project cp where cp.id=cr.object_id and $order(date)=$order(now()) and cp.is_down = 1 and cp.stop_time > $now and cp.status = 1 group by object_id order by sum(hits) desc limit 0,20");
// $res = $project->query("SELECT * FROM (SELECT cr.object_id,sum(hits) total_like FROM cmf_ranking cr WHERE $order(date)=$order(now()) GROUP BY cr.object_id UNION SELECT cp.id,cp.redundance total_like FROM cmf_project cp WHERE cp.is_down = 1 and cp.status = 1 AND cp.stop_time > unix_timestamp()) tmp LEFT JOIN cmf_project cp on cp.id=tmp.object_id GROUP BY object_id ORDER BY total_like DESC LIMIT $firstRow,20");
$res = $project->query("SELECT pro.*,IFNULL(cr.total_hit,0) AS total_like FROM cmf_project AS pro LEFT JOIN (SELECT object_id, SUM(hits) AS total_hit FROM cmf_ranking WHERE $order(date)=$order(now()) GROUP BY object_id) AS cr ON pro.id=cr.object_id HAVING pro.is_down = 1 AND pro.status = 1 AND pro.stop_time > unix_timestamp() ORDER BY total_like DESC LIMIT $firstRow,20");
// $ranking = $project->query("select cp.*,SUM(hits) total_like from cmf_project cp left JOIN cmf_ranking cr on cp.id = cr.object_id WHERE cp.is_down = 1 and cp.status = 1 GROUP BY cp.id ORDER BY total_like desc");
// $count = count($ranking);
// $Page = new \Think\Page($count, 20);
// $data1 = $project->query("select cp.*,SUM(hits) total_like from cmf_project cp left JOIN cmf_ranking cr on cp.id = cr.object_id WHERE $order(date)=$order(now()) and cp.is_down = 1 and cp.status = 1 GROUP BY cp.id ORDER BY total_like desc limit $Page->firstRow, $Page->listRows");
// $data2 = $project->query("select cp.*,SUM(hits) total_like from cmf_project cp left JOIN cmf_ranking cr on cp.id = cr.object_id WHERE cp.is_down = 1 and cp.status = 1 GROUP BY cp.id ORDER BY total_like desc limit $Page->firstRow, $Page->listRows");
// for ($i = 0; $i < $count; $i++) {
// $data['pro'][] = array_merge($data2[$i], $data1[$i]);
// }
// $data['page'] = $Page->show();
// foreach ($ranking as $k => $v) {
// $key_id[] = $v['id'];
// }
// if ($key_id) {
// $num = 20 - count($ranking);
// $where['id'] = array('not in', $key_id);
// $projecting = M('Project')->where($where)->limit(0, $num)->select();
// $data['pro'] = array_merge_recursive($ranking, $projecting);
// } else {
// $data['pro'] = $project->where($where)->order('post_like desc')->limit(0, 20)->select();
// }
// 取出点赞的数量和价格
// $option = M('Options')->where("option_name='site_options'")->find();
// $option01 = json_decode($option['option_value'], true);
// $site_like_price = json_decode(htmlspecialchars_decode($option01['site_like_price']), true);
// foreach ($site_like_price as $k => $v) {
// $like_price[$k] = '赞' . $k . '个(' . $v . '元)';
// }
// $this->assign('data', $data);
// $this->assign('order', $order);
// $this->assign('like_price', $like_price);
// $this->display();die;
} else {
$res = $project->query("SELECT pro.*,IFNULL(cr.total_hit,0) AS total_like FROM cmf_project AS pro LEFT JOIN (SELECT object_id, SUM(hits) AS total_hit FROM cmf_ranking GROUP BY object_id) AS cr ON pro.id=cr.object_id HAVING pro.is_down = 1 AND pro.status = 1 AND pro.stop_time > unix_timestamp() ORDER BY total_like DESC LIMIT $firstRow,20");
}
if ($keywords) {
$res = $project->query("SELECT pro.*,IFNULL(cr.total_hit,0) AS total_like FROM cmf_project AS pro LEFT JOIN (SELECT object_id, SUM(hits) AS total_hit FROM cmf_ranking WHERE $order(date)=$order(now()) GROUP BY object_id) AS cr ON pro.id=cr.object_id HAVING pro.is_down = 1 AND pro.status = 1 AND pro.stop_time > unix_timestamp() AND pro.p_name like '%$keywords%' ORDER BY total_like DESC");
$count = count($res);
}
$Page = new \Think\Page($count, 20);
// foreach ($res as $k => $v) {
// $ids[] = $v['object_id'];
// }
// $map['id'] = array('in', $ids);
// $res2 = $project->where($map)->limit($Page->firstRow . ',' . $Page->listRows)->select();
// foreach ($res2 as $k1 => $v1) {
// foreach ($res as $k2 => $v2) {
// if ($v1['id'] == $v2['object_id']) {
// $res2[$k1]['total_like'] = $res[$k2]['total_like'];
// }
// }
// }
// foreach ($res as $k => $v) {
// $res2[] = $project->where('id=' . $v['object_id'])->find();
// }
// var_dump($res2);die;
// $map['id'] = array('in', $ids);
// $res2 = $project->where($map)->limit($Page->firstRow . ',' . $Page->listRows)->select();
// foreach ($res2 as $k1 => $v1) {
// foreach ($res as $k2 => $v2) {
// if ($v1['id'] == $v2['object_id']) {
// $res2[$k1]['total_like'] = $res[$k2]['total_like'];
// }
// }
// }
// //二维数组排序
// $sort = array(
// 'direction' => 'SORT_DESC', //排序顺序标志 SORT_DESC 降序；SORT_ASC 升序
// 'field' => 'total_like', //排序字段
// );
// $arrSort = array();
// foreach ($res2 as $uniqid => $row) {
// foreach ($row as $key => $value) {
// $arrSort[$key][$uniqid] = $value;
// }
// }
// if ($sort['direction']) {
// array_multisort($arrSort[$sort['field']], constant($sort['direction']), $res2);
// }
$data['pro'] = $res;
$data['page'] = $Page->show();
// $count = $project->where($where)->count();
// $Page = new \Think\Page($count, 10);
// $data['pro'] = $project->where($where)->order('post_like desc')->limit($Page->firstRow . ',' . $Page->listRows)->select();
// $data['page'] = $Page->show();
// 取出点赞的数量和价格
$option = M('Options')->where("option_name='site_options'")->find();
$option01 = json_decode($option['option_value'], true);
$site_like_price = json_decode(htmlspecialchars_decode($option01['site_like_price']), true);
foreach ($site_like_price as $k => $v) {
$like_price[$k] = '赞' . $k . '个(' . $v . '元)';
}
$this->assign(array(
'data' => $data,
'newdata' => $newdata,
'hotdata' => $hotdata,
'order' => $order,
'like_price' => $like_price,
));
$this->display();
}
发表回复