SELECT
od.date,
od.orderCount
FROM
(
SELECT
DATE_FORMAT(order_time,'%Y-%m-%d') date,
count(*) orderCount
FROM
order
WHERE
order_time>'开始时间' and order_time<'结束时间'
GROUP BY DATE_FORMAT(order_time,'%Y-%m-%d')
UNION (SELECT 0, '2017-03-01')
UNION (SELECT 0, '2017-03-02')
UNION (SELECT 0, '2017-03-03')
UNION (SELECT 0, '2017-03-04')
UNION (SELECT 0, '2017-03-05')
UNION (SELECT 0, '2017-03-06')
UNION (SELECT 0, '2017-03-07')
UNION (SELECT 0, '2017-03-08')
-- 很多个UNION ......
) AS od
GROUP BY od.date
ORDER BY od.date ASC
如果有日期表那就用联表就行了，这里是不联表的方法，在程序上做处理后拼接到sql上
public function getLaidianLaifang($proj_ids, $start_date, $end_date, $gjfs, $type){
if(empty($proj_ids)){
return [];
}
$where = " and proj_id in ('".implode("','", $proj_ids)."')";
$rs = $this->dealDate($type, $start_date, $end_date);
$group = $rs['group'];
$union = $rs['union'];
$sql = "select d.gj_cst_sum,d.date_type from (select count(DISTINCT opp_id) as gj_cst_sum,{$group} as date_type from s_opp2gjjl where gjfs = {$gjfs} and gj_date BETWEEN :start_date and :end_date {$where} GROUP BY {$group} {$union})
as d GROUP BY d.date_type ORDER BY d.date_type ASC";
return $this->kfsDb->createCommand($sql, [':start_date' => $start_date, ':end_date' => $end_date.' 23:59:59'])->queryAll();
}
/**
* 处理时间，把缺的补上
* */
private function dealDate($type, $start_date, $end_date, $proj = null){
$union = '';
$group = '';
if($type == 1){
$group = "HOUR(gj_date)";
for($i = 0; $i < 24; $i++){
$union .= " UNION (SELECT 0,{$i} {$proj})";
}
}elseif($type == 3){
$group = "DATE(gj_date)";
for($i = $start_date; $i <= $end_date; $i++){
$union .= " UNION (SELECT 0, '{$i}' {$proj})";
}
}elseif ($type == 4){
$group = "WEEK(gj_date,0)";
$s = date('W',strtotime($start_date));
$e = date('W',strtotime($end_date));
for($i = $s; $i <= $e; $i++){
$union .= " UNION (SELECT 0,{$i} {$proj})";
}
}elseif ($type == 5){
$group = "MONTH(gj_date)";
$s = date('n',strtotime($start_date));
$e = date('n',strtotime($end_date));
for($i = $s; $i <= $e; $i++){
$union .= " UNION (SELECT 0,{$i} {$proj})";
}
}
return ['group' => $group, 'union' => $union];
}
转载请注明原文地址: https://www.6miu.com/read-18906.html
