MySQL中Case When用法详解
最近，在学习Hive基础知识时，遇到了遇到了Case When Else End语法，以前学习MySQL时忽略了这部分知识点，现总结一下相关的知识给大家。首先练习一个例子如下：
一、学生课程成绩统计
1)建表
-
use hiveDemo; -
CREATE TABLE `course` ( -
`id` int, -
`sid` int , -
`course` string, -
`score` int -
) ;
2)插入数据
-
// 字段解释：id, 学号， 课程， 成绩 -
INSERT INTO `course` VALUES (1, 1, 'yuwen', 43); -
INSERT INTO `course` VALUES (2, 1, 'shuxue', 55); -
INSERT INTO `course` VALUES (3, 2, 'yuwen', 77); -
INSERT INTO `course` VALUES (4, 2, 'shuxue', 88); -
INSERT INTO `course` VALUES (5, 3, 'yuwen', 98); -
INSERT INTO `course` VALUES (6, 3, 'shuxue', 65);
3)需求
求：所有数学课程成绩 大于 语文课程成绩的学生的学号
最开始的想法是打算同过自连接的方式来解决，后来咨询了公司的同事，知道了可以case可以用来解决此问题。
a.使用case...when...将不同的课程名称转换成不同的列。
-
create view tmp_course_view as -
select sid, case course when "shuxue" then score else 0 end as shuxue, -
case course when "yuwen" then score else 0 end as yuwen from course; -
select * from tmp_course_view;
b.以sid分组合并取各成绩最大值
-
create view tmp_course_view1 as -
select aa.sid, max(aa.shuxue) as shuxue, max(aa.yuwen) as yuwen from tmp_course_view aa group by sid; -
select * from tmp_course_view1;
c.比较结果
select * from tmp_course_view1 where shuxue > yuwen;
二、Case When Else End用法详解
1)更新
-
update table -
set 字段1=case -
when 条件1 then 值1 -
when 条件2 then 值2 -
else 值3 -
end -
where ……
2)查询
-
select 字段1, 字段2, -
case 字段3 -
when 值1 then 新值 -
when 值2 then 新值 -
end as 重新命名字段3的名字 -
from table -
where …… -
order by ……
在一般的SELECT中，其语法如下：
-
sql 中 case when 语法在这里添加日志标题 - 钱途无梁 - Notebook of 钱途无梁SELECT <myColumnSpec> = -
sql 中 case when 语法在这里添加日志标题 - 钱途无梁 - Notebook of 钱途无梁CASE -
sql 中 case when 语法在这里添加日志标题 - 钱途无梁 - Notebook of 钱途无梁WHEN <A> THEN <somethingA> -
sql 中 case when 语法在这里添加日志标题 - 钱途无梁 - Notebook of 钱途无梁WHEN <B> THEN <somethingB> -
sql 中 case when 语法在这里添加日志标题 - 钱途无梁 - Notebook of 钱途无梁ELSE <somethingE> -
sql 中 case when 语法在这里添加日志标题 - 钱途无梁 - Notebook of 钱途无梁END
CASE可能是 SQL 中被误用最多的关键字之一。虽然你可能以前用过这个关键字来创建字段，但是它还具有更多用法。例如，你可以在 WHERE、GROUP BY和Order By子句中使用CASE。
一：php+mysql 生成统计报表
mysql获取数据的代码：
//结束时间 年月日
$endTime = date('Y-m-d');
//开始时间 年月日 前30天日期
$startTime = date('Y-m-d', (strtotime($endTime) - 30*24*60*60));
//循环的初始值 秒
$start = strtotime($startTime);
//循环的最大值 秒
$end = strtotime($endTime);
//步长 一天 循环的时候按一天的时间作为步长
$step = 24*60*60;
//sql语句 按点击代码 分组查询 按板块编号和点击的代码升序排序
$groupByHitCodeSql = "select type_id,hit_code,hit_name,";
for($i=$start; $i<$end; $i+=$step){
$time = date('Y-m-d', $i);
$groupByHitCodeSql .= " sum(case when DATE_FORMAT(`hit_date`,'%Y-%m-%d') = '{$time}' then `hit_num` else 0 end) as '$time',";
}
$groupByHitCodeSql = trim($groupByHitCodeSql, ',');
$groupByHitCodeSql .= " from tableName where `hit_date`>='{$startTime}' and `hit_date`<='{$endTime}' group by hit_code order by type_id asc,hit_code asc";
//运行程序生成代码
select type_id,hit_code,hit_name,
sum(case when DATE_FORMAT(`hit_date`,'%Y-%m-%d') = '2012-11-11' then `hit_num` else 0 end) as '2012-11-11',
sum(case when DATE_FORMAT(`hit_date`,'%Y-%m-%d') = '2012-11-12' then `hit_num` else 0 end) as '2012-11-12',
sum(case when DATE_FORMAT(`hit_date`,'%Y-%m-%d') = '2012-11-13' then `hit_num` else 0 end) as '2012-11-13',
sum(case when DATE_FORMAT(`hit_date`,'%Y-%m-%d') = '2012-11-14' then `hit_num` else 0 end) as '2012-11-14',
sum(case when DATE_FORMAT(`hit_date`,'%Y-%m-%d') = '2012-11-15' then `hit_num` else 0 end) as '2012-11-15',
sum(case when DATE_FORMAT(`hit_date`,'%Y-%m-%d') = '2012-11-16' then `hit_num` else 0 end) as '2012-11-16',
sum(case when DATE_FORMAT(`hit_date`,'%Y-%m-%d') = '2012-11-17' then `hit_num` else 0 end) as '2012-11-17',
sum(case when DATE_FORMAT(`hit_date`,'%Y-%m-%d') = '2012-11-18' then `hit_num` else 0 end) as '2012-11-18',
sum(case when DATE_FORMAT(`hit_date`,'%Y-%m-%d') = '2012-11-19' then `hit_num` else 0 end) as '2012-11-19',
sum(case when DATE_FORMAT(`hit_date`,'%Y-%m-%d') = '2012-11-20' then `hit_num` else 0 end) as '2012-11-20',
sum(case when DATE_FORMAT(`hit_date`,'%Y-%m-%d') = '2012-11-21' then `hit_num` else 0 end) as '2012-11-21',
sum(case when DATE_FORMAT(`hit_date`,'%Y-%m-%d') = '2012-11-22' then `hit_num` else 0 end) as '2012-11-22',
sum(case when DATE_FORMAT(`hit_date`,'%Y-%m-%d') = '2012-11-23' then `hit_num` else 0 end) as '2012-11-23',
sum(case when DATE_FORMAT(`hit_date`,'%Y-%m-%d') = '2012-11-24' then `hit_num` else 0 end) as '2012-11-24',
sum(case when DATE_FORMAT(`hit_date`,'%Y-%m-%d') = '2012-11-25' then `hit_num` else 0 end) as '2012-11-25',
sum(case when DATE_FORMAT(`hit_date`,'%Y-%m-%d') = '2012-11-26' then `hit_num` else 0 end) as '2012-11-26',
sum(case when DATE_FORMAT(`hit_date`,'%Y-%m-%d') = '2012-11-27' then `hit_num` else 0 end) as '2012-11-27',
sum(case when DATE_FORMAT(`hit_date`,'%Y-%m-%d') = '2012-11-28' then `hit_num` else 0 end) as '2012-11-28',
sum(case when DATE_FORMAT(`hit_date`,'%Y-%m-%d') = '2012-11-29' then `hit_num` else 0 end) as '2012-11-29',
sum(case when DATE_FORMAT(`hit_date`,'%Y-%m-%d') = '2012-11-30' then `hit_num` else 0 end) as '2012-11-30',
sum(case when DATE_FORMAT(`hit_date`,'%Y-%m-%d') = '2012-12-01' then `hit_num` else 0 end) as '2012-12-01',
sum(case when DATE_FORMAT(`hit_date`,'%Y-%m-%d') = '2012-12-02' then `hit_num` else 0 end) as '2012-12-02',
sum(case when DATE_FORMAT(`hit_date`,'%Y-%m-%d') = '2012-12-03' then `hit_num` else 0 end) as '2012-12-03',
sum(case when DATE_FORMAT(`hit_date`,'%Y-%m-%d') = '2012-12-04' then `hit_num` else 0 end) as '2012-12-04',
sum(case when DATE_FORMAT(`hit_date`,'%Y-%m-%d') = '2012-12-05' then `hit_num` else 0 end) as '2012-12-05',
sum(case when DATE_FORMAT(`hit_date`,'%Y-%m-%d') = '2012-12-06' then `hit_num` else 0 end) as '2012-12-06',
sum(case when DATE_FORMAT(`hit_date`,'%Y-%m-%d') = '2012-12-07' then `hit_num` else 0 end) as '2012-12-07',
sum(case when DATE_FORMAT(`hit_date`,'%Y-%m-%d') = '2012-12-08' then `hit_num` else 0 end) as '2012-12-08',
sum(case when DATE_FORMAT(`hit_date`,'%Y-%m-%d') = '2012-12-09' then `hit_num` else 0 end) as '2012-12-09',
sum(case when DATE_FORMAT(`hit_date`,'%Y-%m-%d') = '2012-12-10' then `hit_num` else 0 end) as '2012-12-10'
from juren_shouyes
where `hit_date`>='2012-11-11' and `hit_date`<='2012-12-11'
group by hit_code
order by type_id asc,hit_code asc
//数据库中运行 生成如下表格
二：mysql 按日、周、月、年统计sql语句整理，实现报表统计可视化
最近在做报表统计，前端可视化插件用的是Echarts，个人认为是目前免费插件里的翘楚http://echarts.baidu.com/demo.html#pie-doughnut例子演示
现在重点如何把后台数据源搞定，我采用的是mybatis,所以重点是怎么把sql语句写好。
– –按日
SELECT COUNT(*),DATE(CreateTime) FROM t_voipchannelrecord WHERE YEAR(CreateTime)='2016' GROUP BY DAY(CreateTime)- 1
– –按周
SELECT COUNT(*),WEEK(CreateTime) FROM t_voipchannelrecord WHERE MONTH(CreateTime) = '8' GROUP BY WEEK(CreateTime) - 1
–周一到周五每天的统计结果
SELECT COUNT(*),DAYNAME(CreateTime) FROM t_voipchannelrecord WHERE YEAR(CreateTime) = '2016' GROUP BY DAYNAME(CreateTime) - 1
–统计本周数据
SELECT COUNT(*) FROM t_voipchannelrecord WHERE MONTH(CreateTime) =
MONTH(CURDATE()) AND WEEK(CreateTime) = WEEK(CURDATE())- 1
- 2
–按月统计
SELECT COUNT(*),MONTH(CreateTime) FROM t_voipchannelrecord WHERE YEAR(CreateTime) = '2016' GROUP BY MONTH(CreateTime) - 1
–统计本月数据
SELECT COUNT(*) FROM t_voipchannelrecord WHERE MONTH(CreateTime) =
MONTH(CURDATE()) AND YEAR(CreateTime) = YEAR(CURDATE())- 1
- 2
–按季统计
SELECT COUNT(*),QUARTER(CreateTime) FROM t_voipchannelrecord WHERE YEAR(CreateTime) = '2016' GROUP BY QUARTER(CreateTime) - 1
–按年统计
SELECT COUNT(*),YEAR(CreateTime) FROM t_voipchannelrecord GROUP BY YEAR(CreateTime) - 1
- 2
–时间段(该段参考：出处)
N天内记录
WHERE TO_DAYS(NOW()) - TO_DAYS(时间字段) <= N
当天的记录
where date(时间字段)=date(now())
或
where to_days(时间字段) = to_days(now());
查询一周：
select * from table where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(column_time);
查询一个月：
select * from table where DATE_SUB(CURDATE(), INTERVAL INTERVAL 1 MONTH) <= date(column_time);
查询'06-03'到'07-08'这个时间段内所有过生日的会员：
Select * From user Where
DATE_FORMAT(birthday,'%m-%d') >= '06-03' and DATE_FORMAT(birthday,'%m-%d')
<= '07-08';
统计一季度数据，表时间字段为：savetime
group by concat(date_format(savetime, '%Y '),FLOOR((date_format(savetime, '%m ')+2)/3))
或
select YEAR(savetime)*10+((MONTH(savetime)-1) DIV 3) +1,count(*)
from yourTable
group by YEAR(savetime)*10+((MONTH(savetime)-1) DIV 3) +1;
三：SQL统计实例——mysql实现
网上的经典例题，这里用mysql方言实现。
表格如下：
mysql建表语句：
/*Table structure for table `stuscore` */
DROP TABLE IF EXISTS `stuscore`;
CREATE TABLE `stuscore` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`subject` varchar(20) DEFAULT NULL,
`score` varchar(20) DEFAULT NULL,
`stuid` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
/*Data for the table `stuscore` */
insert into `stuscore`(`id`,`name`,`subject`,`score`,`stuid`) values
(1,'张三','数学','89','1'),
(2,'张三','语文','80','1'),
(3,'张三','英语','70','1'),
(4,'李四','数学','90','2'),
(5,'李四','语文','70','2'),
(6,'李四','英语','80','2'),
(7,'王五','数学','55','3'),
(8,'王五','语文','92','3'),
(9,'王五','英语','74','3'),
(10,'赵六','数学','62','4'),
(11,'赵六','语文','81','4'),
(12,'赵六','英语','93','4');
问题：
1. 计算每个人的总成绩并排名(要求显示字段：姓名，总成绩)
2. 计算每个人的总成绩并排名(要求显示字段: 学号，姓名，总成绩)
3. 计算每个人单科的最高成绩(要求显示字段: 学号，姓名，课程，最高成绩)
4. 计算每个人的平均成绩（要求显示字段: 学号，姓名，平均成绩）
5. 列出各门课程成绩最好的学生(要求显示字段: 学号，姓名,科目，成绩)
6. 列出各门课程成绩最好的两位学生(要求显示字段: 学号，姓名,科目，成绩)
7. 统计如下：
| 学号 | 姓名 | 语文 | 数学 | 英语 | 总分 | 平均分 |
|
|
|
|
|
|
|
|
8．列出各门课程的平均成绩（要求显示字段：课程，平均成绩）
9．列出数学成绩的排名（要求显示字段：学号，姓名，成绩，排名）
10．列出数学成绩在2-3名的学生（要求显示字段：学号，姓名,科目，成绩）
11．求出李四的数学成绩的排名
12．统计如下：
| 课程 | 不及格（0-59）个 | 良（60-80）个 | 优（81-100）个 |
|
|
|
|
|
答案：
1. 计算每个人的总成绩并排名(要求显示字段：姓名，总成绩)
SELECT a.name, SUM(score) sum_score FROM stuscore a GROUP BY a.name
2. 计算每个人的总成绩并排名(要求显示字段: 学号，姓名，总成绩)
SELECT a.stuid, a.name, SUM(score) sum_score FROM stuscore a GROUP BY a.name
3. 计算每个人单科的最高成绩(要求显示字段: 学号，姓名，课程，最高成绩)
SELECT a.stuid, a.name, a.subject, a.score FROM stuscore a
JOIN (
SELECT stuid, MAX(score) max_score FROM stuscore GROUP BY stuid
)b ON a.stuid=b.stuid
WHERE a.score=b.max_score
4. 计算每个人的平均成绩（要求显示字段: 学号，姓名，平均成绩）
SELECT DISTINCT a.stuid, a.name, b.avg_score FROM stuscore a
JOIN (
SELECT stuid, AVG(score) avg_score FROM stuscore GROUP BY stuid
)b ON a.stuid=b.stuid
5. 列出各门课程成绩最好的学生(要求显示字段: 学号，姓名,科目，成绩)
SELECT DISTINCT a.stuid, a.name, a.subject, a.score FROM stuscore a
JOIN (
SELECT subject, MAX(score) max_score FROM stuscore GROUP BY subject
)b ON a.subject=b.subject
WHERE a.score=b.max_score
6. 列出各门课程成绩最好的两位学生(要求显示字段: 学号，姓名,科目，成绩)
SELECT DISTINCT a.stuid, a.name, a.subject, a.score FROM stuscore a
WHERE (
SELECT COUNT(1) FROM stuscore b
WHERE b.subject=a.subject AND b.score>=a.score
) <= 2
ORDER BY a.subject ASC, a.score DESC
7. 统计如下：
| 学号 | 姓名 | 语文 | 数学 | 英语 | 总分 | 平均分 |
|
|
|
|
|
|
|
|
SELECT a.stuid 学号, a.name 姓名,
SUM(CASE WHEN a.subject='语文' THEN score ELSE 0 END)语文,
SUM(CASE WHEN a.subject='数学' THEN score ELSE 0 END)数学,
SUM(CASE WHEN a.subject='英语' THEN score ELSE 0 END)英语,
SUM(score)总分, (SUM(score)/COUNT(1))平均分
FROM stuscore a GROUP BY a.stuid
8．列出各门课程的平均成绩（要求显示字段：课程，平均成绩）
SELECT a.subject, AVG(score) avg_score FROM stuscore a GROUP BY a.subject
9．列出数学成绩的排名（要求显示字段：学号，姓名，成绩，排名）
SELECT a.*, @var:=@var+1 rank
FROM (
SELECT stuid, name, score FROM stuscore
WHERE subject='数学' ORDER BY score DESC
)a, (SELECT @var:=0)b
10．列出数学成绩在2-3名的学生（要求显示字段：学号，姓名,科目，成绩）
SELECT t.* FROM (
SELECT a.*, @var:=@var+1 rank
FROM (
SELECT stuid, NAME, score FROM stuscore
WHERE SUBJECT='数学' ORDER BY score DESC
)a, (SELECT @var:=0)b
)t WHERE t.rank IN (2,3)
11．求出李四的数学成绩的排名
SELECT t.* FROM (
SELECT a.*, @var:=@var+1 rank
FROM (
SELECT stuid, name, score FROM stuscore
WHERE subject='数学' ORDER BY score DESC
)a, (SELECT @var:=0)b
)t WHERE t.name='李四'
12．统计如下：
| 课程 | 不及格（0-59）个 | 良（60-80）个 | 优（81-100）个 |
|
|
|
|
|
SELECT a.subject 课程,
(SELECT COUNT(1) FROM stuscore WHERE subject=a.subject AND score<60)不及格,
(SELECT COUNT(1) FROM stuscore WHERE subject=a.subject AND score BETWEEN 60 AND 80)良,
(SELECT COUNT(1) FROM stuscore WHERE subject=a.subject AND score>80)优
FROM stuscore a GROUP BY a.subject
四：Mysql实现统计查询
统计查询表中某一个字段每种类型的数量。
例：表内数据如下
现想要统计age 25岁前后的数量与百分比：
五：Mysql统计每年每个月的数据(前端页面统计图实现)
最终想实现的效果图，在这里就不多废话了，直接上效果图，由于测试数据有几个月是为0的，所以数据图看着会有点怪怪。
另外，个人使用了ssm框架(Spring、Mybatis、SpringMVC)，可根据自己的项目情况修改
接下来是数据库的两个表，这里直接给你们代码了，你们根据自己的需求更改即可
1 -- 会员充值表
2 CREATE TABLE rechargeinfo(
3 r_num INT PRIMARY KEY COMMENT '充值编号',
4 v_card VARCHAR(12) COMMENT '会员卡号',
5 r_recharge DOUBLE COMMENT '充值金额',
6 r_payway VARCHAR(20) COMMENT '支付方式',
7 o_id INT COMMENT '操作员工编号',
8 r_time DATETIME COMMENT '交易时间',
9 r_remark VARCHAR(50) COMMENT '交易备注',
10 FOREIGN KEY (o_id) REFERENCES operatorinfo(o_id)
11 )ENGINE = InnoDB COMMENT '会员充值信息表';
12 alter table rechargeinfo change r_time r_time timestamp not null default NOW();
13
14 --停车登记表
15
16 CREATE TABLE parkinginfo(
17 p_num INT PRIMARY KEY COMMENT '停车编号',
18 c_carplate VARCHAR(20) NOT NULL COMMENT '车牌号',
19 p_card VARCHAR(20) COMMENT '停车牌号',
20 p_picture VARCHAR(50) COMMENT '进场拍摄图',
21 p_entrytime Date COMMENT '进场时间',
22 p_leavetime Date COMMENT '出场时间',
23 p_type VARCHAR(10) COMMENT '客户类型',
24 p_cost Double COMMENT '停车费用',
25 p_payway VARCHAR(20) COMMENT '支付方式',
26 v_card VARCHAR(12) COMMENT '会员卡号',
27 v_phone VARCHAR(12) COMMENT '临时客户手机号码',
28 p_condition VARCHAR(20) DEFAULT '正在停车中' COMMENT '状态',
29 p_remark VARCHAR(50) COMMENT '备注'
30
31 )ENGINE = InnoDB COMMENT '停车信息表';
32 alter table parkinginfo change p_entrytime p_entrytime timestamp not null default NOW();
接下来就是重点了：
SQL语句，只需要传入一个参数（年份）即可， 这个是统计会员充值表的，另一张表同理
1 select
2 sum(case month(r_time) when '1' then r_recharge else 0 end) as Jan,
3 sum(case month(r_time) when '2' then r_recharge else 0 end) as Feb,
4 sum(case month(r_time) when '3' then r_recharge else 0 end) as Mar,
5 sum(case month(r_time) when '4' then r_recharge else 0 end) as Apr,
6 sum(case month(r_time) when '5' then r_recharge else 0 end) as May,
7 sum(case month(r_time) when '6' then r_recharge else 0 end) as June,
8 sum(case month(r_time) when '7' then r_recharge else 0 end) as July,
9 sum(case month(r_time) when '8' then r_recharge else 0 end) as Aug,
10 sum(case month(r_time) when '9' then r_recharge else 0 end) as Sept,
11 sum(case month(r_time) when '10' then r_recharge else 0 end) as Oct,
12 sum(case month(r_time) when '11' then r_recharge else 0 end) as Nov,
13 sum(case month(r_time) when '12' then r_recharge else 0 end) as Dece
14 from rechargeinfo
15 where year(r_time)='2019';
效果图：可以看到，每个月的数据已经查出来了
接下来给出的是Dao层代码，service层就不写了
import java.util.Map;
public interface TotalDao {
Map<String,Double> getRechargeTotal(String toyear);
Map<String,Double> getParkingTotal(String toyear);
}
以及Mapper文件代码：注意，这里的结果类型一定要是java.util.LinkedHashMap, 如果是HashMap，是不会报错，但是顺序会乱，So，，你懂得。
<!-- 统计充值-->
<select id="getRechargeTotal" parameterType="String" resultType="java.util.LinkedHashMap">
select
sum(case month(r_time) when '1' then r_recharge else 0 end) as Jan,
sum(case month(r_time) when '2' then r_recharge else 0 end) as Feb,
sum(case month(r_time) when '3' then r_recharge else 0 end) as Mar,
sum(case month(r_time) when '4' then r_recharge else 0 end) as Apr,
sum(case month(r_time) when '5' then r_recharge else 0 end) as May,
sum(case month(r_time) when '6' then r_recharge else 0 end) as June,
sum(case month(r_time) when '7' then r_recharge else 0 end) as July,
sum(case month(r_time) when '8' then r_recharge else 0 end) as Aug,
sum(case month(r_time) when '9' then r_recharge else 0 end) as Sept,
sum(case month(r_time) when '10' then r_recharge else 0 end) as Oct,
sum(case month(r_time) when '11' then r_recharge else 0 end) as Nov,
sum(case month(r_time) when '12' then r_recharge else 0 end) as Dece
from rechargeinfo
where year(r_time)=#{toyear};
</select>
<!--统计停车-->
<select id="getParkingTotal" parameterType="String" resultType="java.util.LinkedHashMap">
select
sum(case month(p_leavetime) when '1' then p_cost else 0 end) as Jan,
sum(case month(p_leavetime) when '2' then p_cost else 0 end) as Feb,
sum(case month(p_leavetime) when '3' then p_cost else 0 end) as Mar,
sum(case month(p_leavetime) when '4' then p_cost else 0 end) as Apr,
sum(case month(p_leavetime) when '5' then p_cost else 0 end) as May,
sum(case month(p_leavetime) when '6' then p_cost else 0 end) as June,
sum(case month(p_leavetime) when '7' then p_cost else 0 end) as July,
sum(case month(p_leavetime) when '8' then p_cost else 0 end) as Aug,
sum(case month(p_leavetime) when '9' then p_cost else 0 end) as Sept,
sum(case month(p_leavetime) when '10' then p_cost else 0 end) as Oct,
sum(case month(p_leavetime) when '11' then p_cost else 0 end) as Nov,
sum(case month(p_leavetime) when '12' then p_cost else 0 end) as Dece
from parkinginfo
where year(p_leavetime)=#{toyear} and p_condition='交易完成' ;
</select>
</mapper>
Control层：
1 //统计页面
2 @RequestMapping("/totalui")
3 public ModelAndView test(@RequestParam(value ="toyear",required = false,defaultValue = "2019")String toyear){
4 ModelAndView mv = new ModelAndView();
5 // get all data
6 Map<String, Double> rechargeTotal = service.getRechargeTotal(toyear);
7 Map<String,Double> pachargeTotal = service.getParkingTotal(toyear);
8 // test
9 System.out.println("测试所有数据： "+rechargeTotal.values()+" ");
10 System.out.println("测试所有数据 : "+ pachargeTotal.values()+" ");
11
12 // 统计
13 Map<String,Double> datatotal = new LinkedHashMap<>();
14 for(String key:rechargeTotal.keySet()){
15 if(pachargeTotal.containsKey(key)){
16 datatotal.put(key, rechargeTotal.get(key)+pachargeTotal.get(key));
17 }
18 }
19 System.out.println("合并后的数据！！！"+datatotal.values());
20
21 // set atrr
22 mv.addObject("redata",rechargeTotal.values());
23 mv.addObject("padata",pachargeTotal.values());
24 mv.addObject("totaldata",datatotal.values());
25 mv.setViewName("Income");
26 return mv;
27
28 }
前端页面显示图代码：
1 <script>
2 var chart = Highcharts.chart('container', {
3 chart: {
4 type: 'column'
5 },
6 title: {
7 text: '洱海湾停车场营业额统计'
8 },
9
10 legend: {
11 align: 'right',
12 verticalAlign: 'middle',
13 layout: 'vertical'
14 },
15 xAxis: {
16 categories: ['一月', '二月', '三月', '四月', '五月', '六月', '七月', '八月', '九月', '十月', '十一月', '十二月'],
17 labels: {
18 x: -10
19 }
20 },
21 yAxis: {
22 allowDecimals: false,
23 title: {
24 text: '金额'
25 }
26 },
27 series: [{
28 name: '会员充值收入',
29 data: ${redata}
30 }, {
31 name: '停车收入',
32 data: ${padata}
33 }, {
34 name: '合计收入',
35 data: ${totaldata}
36 }],
37 responsive: {
38 rules: [{
39 condition: {
40 maxWidth: 1500
41 },
42 chartOptions: {
43 legend: {
44 align: 'center',
45 verticalAlign: 'bottom',
46 layout: 'horizontal'
47 },
48 yAxis: {
49 labels: {
50 align: 'left',
51 x: 0,
52 y: -5
53 },
54 title: {
55 text: '￥金额以元为单位'
56 }
57 },
58 subtitle: {
59 text: null
60 },
61 credits: {
62 enabled: false
63 }
64 }
65 }]
66 }
67 });
68
69 </script>
大功告成，不懂得可以直接评论咨询！！！！！
862
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
