目录
1、添加测试表和数据
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for shipment
-- ----------------------------
DROP TABLE IF EXISTS `shipment`;
CREATE TABLE `shipment` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`shipment_year` int(255) NULL DEFAULT NULL COMMENT '销售年份',
`shipment_month` int(255) NULL DEFAULT NULL COMMENT '销售月份',
`shipment_value` int(255) NULL DEFAULT NULL COMMENT '销售量',
`create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 14 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci COMMENT = '手机-销售量' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of shipment
-- ----------------------------
INSERT INTO `shipment` VALUES (1, 2020, 1, 2365, '2022-07-15 10:24:15');
INSERT INTO `shipment` VALUES (2, 2020, 2, 1254, '2022-07-15 10:24:27');
INSERT INTO `shipment` VALUES (3, 2020, 3, 3654, '2022-07-15 10:24:39');
INSERT INTO `shipment` VALUES (4, 2020, 4, 2547, '2022-07-15 10:24:51');
INSERT INTO `shipment` VALUES (5, 2021, 1, 365, '2022-07-15 10:24:15');
INSERT INTO `shipment` VALUES (6, 2021, 2, 247, '2022-07-15 10:24:27');
INSERT INTO `shipment` VALUES (7, 2021, 3, 685, '2022-07-15 10:24:39');
INSERT INTO `shipment` VALUES (8, 2021, 4, 324, '2022-07-15 10:24:51');
INSERT INTO `shipment` VALUES (9, 2022, 1, 1587, '2022-07-15 10:24:15');
INSERT INTO `shipment` VALUES (10, 2022, 2, 2158, '2022-07-15 10:24:27');
INSERT INTO `shipment` VALUES (11, 2022, 3, 3687, '2022-07-15 10:24:39');
INSERT INTO `shipment` VALUES (12, 2022, 4, 1965, '2022-07-15 10:24:51');
INSERT INTO `shipment` VALUES (13, 2022, 5, 254, '2022-07-15 10:32:12');
SET FOREIGN_KEY_CHECKS = 1;2、sql查询实现
-- 行转列
SELECT
shipment_year,
sum( CASE WHEN shipment_month = 1 THEN shipment_value ELSE 0 END ) AS 一月,
sum( CASE WHEN shipment_month = 2 THEN shipment_value ELSE 0 END ) AS 二月,
sum( CASE WHEN shipment_month = 3 THEN shipment_value ELSE 0 END ) AS 三月,
sum( CASE WHEN shipment_month = 4 THEN shipment_value ELSE 0 END ) AS 四月,
sum( CASE WHEN shipment_month = 5 THEN shipment_value ELSE 0 END ) AS 五月
FROM
`shipment`
GROUP BY
shipment_year;
-- 统计年份所有销售
SELECT
shipment_year,
sum(shipment_value) AS total
FROM
`shipment`
GROUP BY
shipment_year;数据结果如下：
3、Java实现方式
3.1 添加依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.3</version>
</dependency>
<dependencies>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
</dependencies>3.2 数据实体类
package com.shucha.deveiface.biz.test;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.util.Date;
/**
* @author tqf
* @Description 手机销售量
* @Version 1.0
* @since 2022-07-15 10:46
*/
@Data
public class Shipment {
@ApiModelProperty(value = "主键")
private Long id;
@ApiModelProperty(value = "销售年份")
private String shipmentYear;
@ApiModelProperty(value = "销售月份")
private Integer shipmentMonth;
@ApiModelProperty(value = "销售量")
private Integer shipmentValue;
@ApiModelProperty(value = "创建时间")
private Date createTime;
}
package com.shucha.deveiface.biz.test;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.util.List;
/**
* @author tqf
* @Description
* @Version 1.0
* @since 2022-07-15 10:57
*/
@Data
public class ShipmentDTO {
@ApiModelProperty(value = "销售年份")
private String shipmentYear;
@ApiModelProperty(value = "销售年份")
private List<Shipment> dataList;
}
3.3 实现方法
package com.shucha.deveiface.biz.test;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
/**
* @author tqf
* @Description 行转列
* @Version 1.0
* @since 2022-07-15 10:52
*/
public class ShipmentTest {
private final static String data = "[{\"id\":\"1\",\"shipmentYear\":\"2020\",\"shipmentMonth\":1,\"shipmentValue\":\"2365\",\"createTime\":\"2022-07-15 10:24:15\"},{\"id\":\"2\",\"shipmentYear\":\"2020\",\"shipmentMonth\":2,\"shipmentValue\":\"1254\",\"createTime\":\"2022-07-15 10:24:27\"},{\"id\":\"3\",\"shipmentYear\":\"2020\",\"shipmentMonth\":3,\"shipmentValue\":\"3654\",\"createTime\":\"2022-07-15 10:24:39\"},{\"id\":\"4\",\"shipmentYear\":\"2020\",\"shipmentMonth\":4,\"shipmentValue\":\"2547\",\"createTime\":\"2022-07-15 10:24:51\"},{\"id\":\"5\",\"shipmentYear\":\"2021\",\"shipmentMonth\":1,\"shipmentValue\":\"365\",\"createTime\":\"2022-07-15 10:24:15\"},{\"id\":\"6\",\"shipmentYear\":\"2021\",\"shipmentMonth\":2,\"shipmentValue\":\"247\",\"createTime\":\"2022-07-15 10:24:27\"},{\"id\":\"7\",\"shipmentYear\":\"2021\",\"shipmentMonth\":3,\"shipmentValue\":\"685\",\"createTime\":\"2022-07-15 10:24:39\"},{\"id\":\"8\",\"shipmentYear\":\"2021\",\"shipmentMonth\":4,\"shipmentValue\":\"324\",\"createTime\":\"2022-07-15 10:24:51\"},{\"id\":\"9\",\"shipmentYear\":\"2022\",\"shipmentMonth\":1,\"shipmentValue\":\"1587\",\"createTime\":\"2022-07-15 10:24:15\"},{\"id\":\"10\",\"shipmentYear\":\"2022\",\"shipmentMonth\":2,\"shipmentValue\":\"2158\",\"createTime\":\"2022-07-15 10:24:27\"},{\"id\":\"11\",\"shipmentYear\":\"2022\",\"shipmentMonth\":3,\"shipmentValue\":\"3687\",\"createTime\":\"2022-07-15 10:24:39\"},{\"id\":\"12\",\"shipmentYear\":\"2022\",\"shipmentMonth\":4,\"shipmentValue\":\"1965\",\"createTime\":\"2022-07-15 10:24:51\"},{\"id\":\"13\",\"shipmentYear\":\"2022\",\"shipmentMonth\":5,\"shipmentValue\":\"254\",\"createTime\":\"2022-07-15 10:32:12\"}]";
public static void main(String[] args) {
test();
}
public static void test(){
List<Shipment> list = JSONArray.parseArray(data,Shipment.class);
//按照年份分组 按照月份排序
Map<String, List<Shipment>> map = list.stream().sorted(Comparator.comparing(Shipment::getShipmentMonth).reversed())
.collect(Collectors.groupingBy(Shipment::getShipmentYear));
List<ShipmentDTO> newList = new ArrayList<>();
for (Map.Entry<String, List<Shipment>> entry : map.entrySet()) {
//取list首并放入到新list中
ShipmentDTO dto = new ShipmentDTO();
dto.setShipmentYear(entry.getKey());
dto.setDataList(entry.getValue());
newList.add(dto);
}
System.out.println(JSON.toJSONString(newList));
}
}返回的数据格式如下：
[{
"dataList": [{
"createTime": 1657852332000,
"id": 13,
"shipmentMonth": 5,
"shipmentValue": 254,
"shipmentYear": "2022"
}, {
"createTime": 1657851891000,
"id": 12,
"shipmentMonth": 4,
"shipmentValue": 1965,
"shipmentYear": "2022"
}, {
"createTime": 1657851879000,
"id": 11,
"shipmentMonth": 3,
"shipmentValue": 3687,
"shipmentYear": "2022"
}, {
"createTime": 1657851867000,
"id": 10,
"shipmentMonth": 2,
"shipmentValue": 2158,
"shipmentYear": "2022"
}, {
"createTime": 1657851855000,
"id": 9,
"shipmentMonth": 1,
"shipmentValue": 1587,
"shipmentYear": "2022"
}],
"shipmentYear": "2022"
}, {
"dataList": [{
"createTime": 1657851891000,
"id": 8,
"shipmentMonth": 4,
"shipmentValue": 324,
"shipmentYear": "2021"
}, {
"createTime": 1657851879000,
"id": 7,
"shipmentMonth": 3,
"shipmentValue": 685,
"shipmentYear": "2021"
}, {
"createTime": 1657851867000,
"id": 6,
"shipmentMonth": 2,
"shipmentValue": 247,
"shipmentYear": "2021"
}, {
"createTime": 1657851855000,
"id": 5,
"shipmentMonth": 1,
"shipmentValue": 365,
"shipmentYear": "2021"
}],
"shipmentYear": "2021"
}, {
"dataList": [{
"createTime": 1657851891000,
"id": 4,
"shipmentMonth": 4,
"shipmentValue": 2547,
"shipmentYear": "2020"
}, {
"createTime": 1657851879000,
"id": 3,
"shipmentMonth": 3,
"shipmentValue": 3654,
"shipmentYear": "2020"
}, {
"createTime": 1657851867000,
"id": 2,
"shipmentMonth": 2,
"shipmentValue": 1254,
"shipmentYear": "2020"
}, {
"createTime": 1657851855000,
"id": 1,
"shipmentMonth": 1,
"shipmentValue": 2365,
"shipmentYear": "2020"
}],
"shipmentYear": "2020"
}]
3938
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
