我正在为我制作的招聘网站编写一个报告系统。到目前为止，我已经设法让下面的功能来提取给定时间段内的总作业数，以天、周、月或年分组
我想做的是浏览同一时期的所有工作申请。工作申请存储在另一个名为 job_application 的表中，包含job_application_id、job_id和date_added字段
有没有办法在同一查询中提取数据？SQL开始炸毁我的大脑，所以任何帮助将不胜感激！
public function getJobReport($data = array()) {
$sql = "SELECT MIN(tmp.date_added) AS date_start, MAX(tmp.date_added) AS date_end, COUNT(tmp.job_id) AS `jobs` FROM (SELECT j.job_id, j.date_added FROM `" . DB_PREFIX . "job` j WHERE 1";
if (isset($data['date_start']) && $data['date_start']) {
$sql .= " AND DATE(j.date_added) >= '" . $this->db->escape($data['date_start']) . "'";
}
if (isset($data['date_end']) && $data['date_end']) {
$sql .= " AND DATE(j.date_added) <= '" . $this->db->escape($data['date_end']) . "'";
}
$sql .= " GROUP BY j.job_id) tmp";
if (isset($data['group'])) {
$group = $data['group'];
} else {
$group = 'week';
}
switch($group) {
case 'day';
$sql .= " GROUP BY DAY(tmp.date_added)";
break;
default:
case 'week':
$sql .= " GROUP BY WEEK(tmp.date_added)";
break;
case 'month':
$sql .= " GROUP BY MONTH(tmp.date_added)";
break;
case 'year':
$sql .= " GROUP BY YEAR(tmp.date_added)";
break;
}
if (isset($data['start']) || isset($data['limit'])) {
if ($data['start'] < 0) {
$data['start'] = 0;
}
if ($data['limit'] < 1) {
$data['limit'] = 20;
}
$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
}
$query = $this->db->query($sql);
return $query->rows;
}
我不容易明白为什么这里需要子查询......此查询使用左联接，并选择输入的开始日期和结束日期作为列，然后选择 GROUP（因此将只有一个结果行）。我略微介绍了您的标识符的细节，以使其更具可读性。
...
$date_resolution_function = "DAY"; // set via a switch similar to yours
...
SELECT
$date_resolution_function(apps.date_added),
count(jobs.id) as numb_jobs,
count(apps.id) as num_apps
FROM
jobs JOIN job_application as apps ON jobs.id = apps.job_id
WHERE apps.date_added BETWEEN $start_date AND $end_date
GROUP BY $date_resolution_function(apps.date_added)
编辑：请注意，此技术需要设置开始和结束日期...但我强烈建议您无论如何定义合理的默认值。如果您希望默认值较宽，请使用纪元和 +100 年后，或者其他什么......但也许像"本季度"这样的东西更适合你的客户。
EDIT 2（添加日期分辨率）：重要的是要意识到，使用这种查询，您只能在作业或应用程序上获得准确的日期选择，另一个将是"全部"。我选择了我认为更明智的选择，选择申请日期并包括所有工作，无论它们何时发布（如果他们在指定时间内提交了申请）。如果您想要另一种方式（仅显示在此期间发布的职位），您可以更改传递给日期函数的列，并使 JOIN 成为 LEFT join...但请记住，该职位的申请数量数据将针对整个时间段，而不仅仅是发布作业的日/周。