如何将数据库某列的值（如日期）作为表格的列名

如何将数据库某列的值（如日期）作为表格的列名

需求：前端需要展示如下的二维表格，其中表格的日期是数据库表里data的值，每行不同的值是表格里的字段值，也就是需要将表里的数据行转列，列转行，本来想着通过sql和mybatis映射完成转换，但是搜了一下没有找到自己想要的方法，只好通过java代码来完成转换。


前端代码：

<template>
<div>
<el-form :model="searchForm" ref="searchForm" size="small" :inline="true" label-width="68px">
<el-form-item label="日期" prop="date">
<el-date-picker
v-model="searchForm.date"
type="daterange"
range-separator="至"
start-placeholder="开始日期"
end-placeholder="结束日期"
/>
el-form-item>
<el-form-item>
<el-button type="primary" icon="el-icon-search" size="mini" @click="query">搜索el-button>
<el-button type="info" icon="el-icon-download" size="mini" @click="upload">导出el-button>
<el-button icon="el-icon-refresh" size="mini" @click="resetQuery">重置el-button>
el-form-item>
el-form>

<el-table :data="usdDataList" border stripe v-loading="loading">
<el-table-column prop="date" label="日期" width="155" align="center">
<el-table-column prop="d0" label="项目USD" width="155" align="center">el-table-column>
el-table-column>
<el-table-column
v-for="(item,index) in listLabel"
:key="index"
:prop="item.prop"
:label="item.label"
/>
el-table>
<el-table :data="ureDataList" border stripe v-loading="loading">
<el-table-column prop="date" label="日期" width="155" align="center">
<el-table-column prop="d0" label="项目EUR" width="155" align="center">el-table-column>
el-table-column>
<el-table-column
v-for="(item,index) in listLabel"
:key="index"
:prop="item.prop"
:label="item.label"
/>
el-table>
div>
template>
<script>
import Api from "@/constants/Api.js";
import { getDate, getNextDate, parseTime } from "@/utils/utils";
export default {
name: "zmqldxcsb",
data() {
return {
listLabel: [],
usdDataList: [],
ureDataList: [],
searchForm: {
date: [getDate(0), getDate(30)]
},
loading: false
};
},
created() {
this.query();
},
methods: {
resetQuery() {
this.searchForm = {
date: [getDate(0), getDate(30)]
};
},
query() {
this.loading = true;
this.$axios({
method: "post",
url: Api.freeTradMobility,
data: {
startTime: parseTime(this.searchForm.date[0], "{y}{m}{d}"),
endTime: parseTime(this.searchForm.date[1], "{y}{m}{d}")
}
})
.then(res => {
this.listLabel = res.labelList;
this.usdDataList = res.dataMap["USD"];
this.ureDataList = res.dataMap["EUR"];
this.loading = false;
})
.catch(error => {
console.log(error.message);
this.$message.error("查询数据出错！");
this.loading = false;
});
}
};
script>
<style scoped>
/* ::v-deep .el-table thead.is-group th {
background: none;
padding: 0px;
} */
::v-deep .el-table thead.is-group tr:first-of-type th:first-of-type {
border-bottom: none;
}
::v-deep .el-table thead.is-group tr:first-of-type th:first-of-type div.cell {
text-align: right;
}
::v-deep .el-table thead.is-group tr:last-of-type th:first-of-type div.cell {
text-align: left;
}
::v-deep .el-table thead.is-group tr:first-of-type th:first-of-type:before {
content: "";
position: absolute;
width: 1px;
height: 100px;
top: 0;
left: 0;
background-color: grey;
opacity: 0.2;
display: block;
transform: rotate(-43deg);
transform: rotate(-70deg);
-webkit-transform-origin: top;
transform-origin: top;
}
::v-deep .el-table thead.is-group tr:last-of-type th:first-of-type:before {
content: "";
position: absolute;
width: 1px;
height: 100px;
bottom: 0;
right: 0;
background-color: grey;
opacity: 0.2;
display: block;
transform: rotate(-45deg);
transform: rotate(-70deg);
-webkit-transform-origin: bottom;
transform-origin: bottom;
}
::v-deep .el-table thead.is-group th {
height: 27.4px;
}
style>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140

每个表格的代码：

<el-table :data="usdDataList" border stripe v-loading="loading">
<el-table-column prop="date" label="日期" width="155" align="center">
<el-table-column prop="d0" label="项目USD" width="155" align="center">el-table-column>
el-table-column>
<el-table-column
v-for="(item,index) in listLabel"
:key="index"
:prop="item.prop"
:label="item.label"
/>
el-table>
1
2
3
4
5
6
7
8
9
10
11

下面是需要的数据格式，其中日期是动态的，所以日期和每行的数据是分开的，日期的数据格式：

"labelList": [
{
"label": "20231024",
"prop": "d20231024"
},
{
"label": "20231025",
"prop": "d20231025"
},
{
"label": "20231026",
"prop": "d20231026"
},
{
"label": "20231027",
"prop": "d20231027"
},
{
"label": "20231028",
"prop": "d20231028"
}
],
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

label就是每列展示的日期值，prop就是每行绑定的数据。

表格数据的格式：

[
{
"d20231028": "10",
"d0": "日出余额",
"d20231027": "10",
"d20231026": "10"
},
{
"d20231028": "10",
"d0": "总行2036",
"d20231027": "10",
"d20231026": "10"
},
{
"d20231028": "10",
"d0": "即远期",
"d20231027": "10",
"d20231026": "10"
},
{
"d20231028": "10",
"d0": "掉期",
"d20231027": "10",
"d20231026": "10"
},
{
"d20231028": "10",
"d0": "拆借",
"d20231027": "10",
"d20231026": "10"
},
{
"d20231028": "10",
"d0": "回购",
"d20231027": "10",
"d20231026": "10"
},
{
"d20231028": "10",
"d0": "TRS",
"d20231027": "10",
"d20231026": "10"
},
{
"d20231028": "10",
"d0": "债券到期",
"d20231027": "10",
"d20231026": "10"
},
{
"d20231028": "10",
"d0": "债券投资",
"d20231027": "10",
"d20231026": "10"
},
{
"d20231028": "10",
"d0": "客户进出款（合计）",
"d20231027": "10",
"d20231026": "10"
},
{
"d20231028": "vdvdsvd",
"d0": "备注",
"d20231027": "vdvdsvd",
"d20231026": "vdvdsvd"
},
{
"d20231028": "10",
"d0": "日终余额",
"d20231027": "10",
"d20231026": "10"
}
]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74

其中d0就是表格代码第三行的prop="d0"绑定的值，其余的就是每个日期绑定的值。
针对这两种格式，我们在java代码中就是需要把查到的数据进行组装。

java代码如下。

@Autowired
private DaoSupport daoSupport;

//方法入口
public FrnFreeTradMobilityOut query(FrnFreeTradMobilityIn input) {
//处理时间
String startDate = input.getStartTime();
String endDate = input.getEndTime();
//生成日期的集合
List<Label> labels = createdLabelList(startDate, endDate);

FrnFreeTradMobilityOut out = new FrnFreeTradMobilityOut();
out.setLabelList(labels);
Map<String, Object> queryMap = new HashMap<>();
queryMap.put("startDate", startDate);
queryMap.put("endDate", endDate);

//查询表格数据
List<FrnFreetradMobilityPo> list = daoSupport.selectList(FrnFreetradMobilityPo.class.getName() + ".select", queryMap);

//组装数据，因为我是多个表格，所以采用map形式，key为每个表格的名称，值为表格数据，如果只有一个表格，可以用返回list
Map<String, List<Map<String, String>>> dataMap = createdDataMap(list);
out.setDataMap(dataMap);
return out;
}


private Map<String, List<Map<String, String>>> createdDataMap(List<FrnFreetradMobilityPo> list) {
Map<String, List<Map<String, String>>> result = new HashMap<>();
//循环处理每条记录
for (int i = 0; i < list.size(); i++) {
FrnFreetradMobilityPo frnFreetradMobilityPo = list.get(i);
String project = frnFreetradMobilityPo.getProject();

//判断当前数据所在的表格是否在map中，不存在就初始化
if (!result.containsKey(project)) {
initDataMap(result, project);
}
List<Map<String, String>> dataList = result.get(project);
//如下map是每行的数据，rcyeMap对应的是日出余额行的数据，其余的同理
Map<String, String> rcyeMap = dataList.get(0);
Map<String, String> zhMap = dataList.get(1);
Map<String, String> jyqMap = dataList.get(2);
Map<String, String> dqiMap = dataList.get(3);
Map<String, String> cjieMap = dataList.get(4);
Map<String, String> hgouMap = dataList.get(5);
Map<String, String> TRSMap = dataList.get(6);
Map<String, String> zqdqMap = dataList.get(7);
Map<String, String> zqtzMap = dataList.get(8);
Map<String, String> khjckMap = dataList.get(9);
Map<String, String> bzMap = dataList.get(10);
Map<String, String> rzyeMap = dataList.get(11);

String key = "d" + frnFreetradMobilityPo.getDate();
rcyeMap.put(key, frnFreetradMobilityPo.getRcye());
zhMap.put(key, frnFreetradMobilityPo.getZh());
jyqMap.put(key, frnFreetradMobilityPo.getJyq());
dqiMap.put(key, frnFreetradMobilityPo.getDqi());
cjieMap.put(key, frnFreetradMobilityPo.getCjie());
hgouMap.put(key, frnFreetradMobilityPo.getHgou());
TRSMap.put(key, frnFreetradMobilityPo.getTrs());
zqdqMap.put(key, frnFreetradMobilityPo.getZqdq());
zqtzMap.put(key, frnFreetradMobilityPo.getZqtz());
khjckMap.put(key, frnFreetradMobilityPo.getKhjck());
bzMap.put(key, frnFreetradMobilityPo.getBz());
rzyeMap.put(key, frnFreetradMobilityPo.getRzye());

//eur的表比别的多一列，这时进行特殊处理
if (FrnConstans.EUR.equals(project)) {
Map<String, String> xxzdMap = dataList.get(12);
xxzdMap.put(key, frnFreetradMobilityPo.getXzzd());
}
result.put(list.get(i).getProject(), dataList);
}
if (result.containsKey("EUR")) {
result.get("EUR").add(1, result.get("EUR").get(12));
result.get("EUR").remove(13);
}

return result;
}

//每行数据的初始化
private void initDataMap(Map<String, List<Map<String, String>>> result, String project) {
List date = new ArrayList<>();
Map<String, String> rcyeMap = new HashMap<>();
Map<String, String> zhMap = new HashMap<>();
Map<String, String> jyqMap = new HashMap<>();
Map<String, String> dqiMap = new HashMap<>();
Map<String, String> cjieMap = new HashMap<>();
Map<String, String> hgouMap = new HashMap<>();
Map<String, String> TRSMap = new HashMap<>();
Map<String, String> zqdqMap = new HashMap<>();
Map<String, String> zqtzMap = new HashMap<>();
Map<String, String> khjckMap = new HashMap<>();
Map<String, String> bzMap = new HashMap<>();
Map<String, String> rzyeMap = new HashMap<>();

rcyeMap.put("d0", "日出余额");
zhMap.put("d0", "总行2036");
jyqMap.put("d0", "即远期");
dqiMap.put("d0", "掉期");
cjieMap.put("d0", "拆借");
hgouMap.put("d0", "回购");
TRSMap.put("d0", "TRS");
zqdqMap.put("d0", "债券到期");
zqtzMap.put("d0", "债券投资");
khjckMap.put("d0", "客户进出款（合计）");
bzMap.put("d0", "备注");
rzyeMap.put("d0", "日终余额");

date.add(rcyeMap);
date.add(zhMap);
date.add(jyqMap);
date.add(dqiMap);
date.add(cjieMap);
date.add(hgouMap);
date.add(TRSMap);
date.add(zqdqMap);
date.add(zqtzMap);
date.add(khjckMap);
date.add(bzMap);
date.add(rzyeMap);

//eur表的特殊处理
if (FrnConstans.EUR.equals(project)) {
Map<String, String> xxzdMap = new HashMap<>();
xxzdMap.put("d0", "线下账单");
date.add(xxzdMap);
}
result.put(project, date);
}

//创建日期的数据，Label类的代码在最后
private List<Label> createdLabelList(String startDate, String endDate) {
List<Label> labelList = new ArrayList<>();
LocalDate startTime = LocalDate.of(Integer.parseInt(startDate.substring(0, 4)),Integer.parseInt(startDate.substring(4, 6)), Integer.parseInt(startDate.substring(6, 8)));
LocalDate endTime = LocalDate.of(Integer.parseInt(endDate.substring(0, 4)), Integer.parseInt(endDate.substring(4, 6)), Integer.parseInt(endDate.substring(6, 8)));
long between = ChronoUnit.DAYS.between(startTime, endTime);
for (int i = 0; i <= between; i++) {
LocalDate date = startTime.plusDays(i);
Label label = new Label(date.toString().replace("-", ""), "d" + (date.toString().replace("-", "")));
labelList.add(label);
}
return labelList;
}

@Data
@AllArgsConstructor
public class Label {
private String label;
private String prop;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153

以上就是前后端所有的代码了，如果您有更好的实现方法，欢迎您分享您的方法。