mysql - 如何在sql中返回12个月？

Question

我从这个sql中绘制了一个图表:

SELECT ljj.job_id,
       Sum(CASE
             WHEN ljj.job_type = "0" THEN 1
             ELSE 0
           END)                                        AS jobcount,
       Sum(CASE
             WHEN ljj.job_type = "1" THEN 1
             ELSE 0
           END)                                        AS interncount,
       Monthname(From_unixtime(ljj.job_timepublished)) AS month
FROM   {local_jobs_job} ljj
       INNER JOIN {local_jobs_location} ljl
               ON ljj.job_location = ljl.location_id
       INNER JOIN {local_companydetail} lc
               ON ljj.job_company_userid = lc.userid
WHERE  lc.link = "1"

目前，它仅返回ljj.job_timepublished中记录的月份。我想显示从一月到十二月的所有月份。如果没有数据，则会为月份赋予 0 值。

怎么做？

这是我用来查询数据的php文件:

<?php

require_once('../../config.php');

$data = optional_param('data', null, PARAM_RAW);
$key = optional_param('key', null, PARAM_RAW);
$user = optional_param('user', 0, PARAM_INT);
$id = optional_param('id', 0, PARAM_INT);

global $DB;

///query by location total post
$sql = 'SELECT ljj.job_id, 
SUM(CASE WHEN ljj.job_type = "0" THEN 1 ELSE 0 END) AS jobcount, 
SUM(CASE WHEN ljj.job_type = "1" THEN 1 ELSE 0 END) AS interncount, 
MONTHNAME(FROM_UNIXTIME(ljj.job_timepublished)) AS month FROM {local_jobs_job} ljj 
INNER JOIN {local_jobs_location} ljl ON ljj.job_location = ljl.location_id  
INNER JOIN {local_companydetail} lc ON ljj.job_company_userid = lc.userid 
WHERE lc.link = "1" 
GROUP BY MONTH(FROM_UNIXTIME(ljj.job_timepublished))';


//get the query into record
$data = $DB->get_records_sql($sql);

//put the query into array
$rows = array();

$rows = array_map(function($item) {
return (object) ['c' => [
    (object) ['v' => $item->month, 'f' => null],
    (object) ['v' => intval($item->jobcount), 'f' => null],
    (object) ['v' => intval($item->interncount), 'f' => null]
]];
}, array_values($data));


// prepare return data
$cols = [
(object) ['id' => '', 'label' => 'Month', 'pattern' => '', 'type' => 'string'],
(object) ['id' => '', 'label' => 'Job', 'pattern' => '', 'type' => 'number'],
(object) ['id' => '', 'label' => 'Internship', 'pattern' => '', 'type' => 'number'],

];

$returndata = new stdClass;
$returndata->cols = $cols;
$returndata->rows = $rows;

echo json_encode($returndata);

我使用ajax调用从php文件中调用数据表来绘制图表。

这是sql查询的输出。

{"cols":[{"id":"","label":"Month","pattern":"","type":"string"}, 
{"id":"","label":"Job","pattern":"","type":"number"},
{"id":"","label":"Internship","pattern":"","type":"number"}],
"rows":[{"c":[{"v":"July","f":null},{"v":6,"f":null},{"v":2,"f":null}]},
{"c":[{"v":"August","f":null},{"v":0,"f":null},{"v":3,"f":null}]}]}

Answer 1

您可以使用日历表将每个月引入结果中，并将其作为子查询左连接到您当前拥有的内容。我还认为您应该按月汇总数据，但您目前没有这样做。记住所有这些，我们可以编写以下查询:

SELECT
    t1.monthname,
    COALESCE(t2.jobcount, 0)    AS jobcount,
    COALESCE(t2.interncount, 0) AS interncount
FROM
(
    SELECT 'January' AS monthname UNION ALL
    SELECT 'February'  UNION ALL
    SELECT 'March'     UNION ALL
    SELECT 'April'     UNION ALL
    SELECT 'May'       UNION ALL
    SELECT 'June'      UNION ALL
    SELECT 'July'      UNION ALL
    SELECT 'August'    UNION ALL
    SELECT 'September' UNION ALL
    SELECT 'October'   UNION ALL
    SELECT 'November'  UNION ALL
    SELECT 'December'
) t1
LEFT JOIN
(
    SELECT
        MONTHNAME(FROM_UNIXTIME(ljj.job_timepublished)) AS monthname,
        SUM(CASE WHEN ljj.job_type = "0" THEN 1 ELSE 0 END) AS jobcount,
        SUM(CASE WHEN ljj.job_type = "1" THEN 1 ELSE 0 END) AS interncount   
    FROM {local_jobs_job} ljj
    INNER JOIN {local_jobs_location} ljl
        ON ljj.job_location = ljl.location_id
    INNER JOIN {local_companydetail} lc
        ON ljj.job_company_userid = lc.userid
    WHERE lc.link = '1'
    GROUP BY
        MONTHNAME(FROM_UNIXTIME(ljj.job_timepublished))
) t2
    ON t1.monthname = t2.monthname;

请注意，正如 @Gordon 指出的那样，更明智的聚合可能包括月份和年份。但这会使日历表更加复杂，并且可能需要脚本来生成它。