mysql - 我应该按月比较学生

Question

public function getComparisonStudentsByMonth()
{   
    $last_month = date('m', strtotime('-2 month'));
    $previus_month = date('m', strtotime('-1 month'));

    return
    $this->db->select('MONTH('student_add_date')', date('m'));
    $this->db->where('student_add_date <= DATE_ADD(NOW(),INTERVAL 30 DAYS)');
    $query = $this->db->get('students')->result();
}

问题

用户每月插入新学生和管理页面应显示月份之间的百分比比较。我应该把上个月当作 100% 并与当月进行比较。我理解逻辑，但是如何使用 MySql。

Answer 1

要获取每个月的所有百分比，您可以使用以下命令:

SELECT (MONTH(student_add_date)) AS month_temp, ((count(*) /(SELECT count(*) FROM students)) * 100)percentage from students group by month_temp

要获取两个月之间的数据，您需要在查询中指定月份。例如我将在九月(9)和十月(10)进行搜索

SELECT 
(MONTH(student_add_date)) AS month_temp, 
((count(*) /(SELECT count(*) FROM students WHERE MONTH(student_add_date) = 9 OR MONTH(student_add_date) = 10)) * 100)percentace 
from students 
WHERE 
MONTH(student_add_date) = 9 
OR 
MONTH(student_add_date) = 10 
group by month_temp

在 codeigniter 中，您可以像这样运行自定义查询:

$query = $this->db->query('SELECT 
    (MONTH(student_add_date)) AS month_temp, 
    ((count(*) /(SELECT count(*) FROM students WHERE MONTH(student_add_date) = 9 OR MONTH(student_add_date) = 10)) * 100)percentace 
    from students 
    WHERE 
    MONTH(student_add_date) = 9 
    OR 
    MONTH(student_add_date) = 10 
    group by month_temp');


$result = $query->result_array();

// var_dump($result);

return $result;