在数据可视化过程中，需要对数据进行分段分类的统计，然后传递给图表进行渲染显示。如下图：
数据库字段
正常的数据库字段为浮点类型，如
而实现的是区间分组，如果将数据直接传递给前端，通过js对象数组解析，步骤繁琐，幸好mysql提供的简单快捷的执行方案。
SQL语句
public function getIncome() { global $db, $res; dbc(); @$township = $_POST['township']; //@$township = "神头镇"; $sql = "select COUNT(CASE WHEN orders_income BETWEEN 0 AND 1000 THEN orders_income END) AS income0,COUNT(CASE WHEN orders_income BETWEEN 1000 AND 2000 THEN orders_income END) AS income1,COUNT(CASE WHEN orders_income BETWEEN 2000 AND 3000 THEN orders_income END) AS income2,COUNT(CASE WHEN orders_income BETWEEN 3000 AND 5000 THEN orders_income END) AS income3,COUNT(CASE WHEN orders_income BETWEEN 5000 AND 8000 THEN orders_income END) AS income4,COUNT(CASE WHEN orders_income BETWEEN 8000 AND 12000 THEN orders_income END) AS income5,COUNT(CASE WHEN orders_income >=12000 THEN orders_income END) AS income6 from " . $db->table('orders') . " where 1"; IF ($township != "") { $sql .= " AND orders_workplace =\"" . $township . "\""; } $sql .= " group by orders_province"; $row = $db->queryall($sql); $res["data"] = $row; die(json_encode_lockdata($res)); }
输入的json为：
{"err":"","res":"","data":[{"income0":"5","income1":"0","income2":"0","income3":"4","income4":"4","income5":"4","income6":"3"}]}
前端调用
$.ajax({ type: 'post', async: false, data: {township: township}, url: './api/api.php?act=getIncome&token=3cab7ce4142608c0f40c785b5ab5ca24', dataType: "json", success: function (res) { var dataName = ['无', '1000-2000元', '2000-3000元', '3000-5000元', '5000-8000元', '8000-12000元', '12000元以上']; var dataList = [ {name: dataName[0], value: res.data[0].income0}, {name: dataName[1], value: res.data[0].income1}, {name: dataName[2], value: res.data[0].income2}, {name: dataName[3], value: res.data[0].income3}, {name: dataName[4], value: res.data[0].income4}, {name: dataName[5], value: res.data[0].income5}, {name: dataName[6], value: res.data[0].income6}, ] //过滤数据为0的数据; var v = filterValue(dataList, 0); //console.log(v); //筛选新的坐标轴name; var vn = []; if (v) { for (var i = 0; i < v.length; i++) { vn.push(v[i].name); } } //console.log(vn); //执行渲染函数; getHBar(vn, v, color, id, attri) }, error: function (err) { console.log(err + "请求数据失败!"); } });
Done！
