在车辆租赁行业，数据的高效管理与分析对于企业的运营决策、资源调配及客户服务优化至关重要。自建 Excel 实现多表统计交互，如同为行业装上了效能驱动引擎，助力企业在复杂多变的市场环境中稳健前行。
一、精准资源管理，优化车辆调配
车辆租赁企业拥有多种类型、不同状态的车辆资源，分布于各个租赁站点。通过自建 Excel 并实现多表统计交互，可将车辆信息表、租赁订单表、站点信息表等进行关联分析。例如，在车辆信息表中记录车辆型号、购置时间、维修记录等，租赁订单表记录租车时间、还车时间、客户信息，站点信息表记录各站点位置、可容纳车辆数等。利用 Excel 的函数和数据透视表功能，企业能实时统计各站点不同类型车辆的库存情况，根据订单趋势提前预测车辆需求。如通过多表交互分析发现某站点在旅游旺季对 SUV 车型的租赁需求大增，企业可提前从其他站点调配车辆，避免供不应求的情况，提高车辆利用率，降低运营成本。
二、深度客户洞察，提升服务质量
客户是车辆租赁企业的核心资产，自建 Excel 多表统计交互有助于深入了解客户行为和需求。将客户信息表、租赁历史表、客户反馈表等进行整合分析，企业可以统计客户的租赁频率、租赁时长、偏好车型等信息。例如，通过多表关联发现某些客户经常租赁高端车型且租赁时间较长，企业可针对性地为这些客户提供专属优惠、优先预订权等增值服务，增强客户忠诚度。同时，结合客户反馈表，分析不同车型的客户满意度，找出需要改进的服务环节，如车辆清洁程度、取还车流程便捷性等，从而优化服务质量，提升品牌形象。
三、精细财务分析，支持决策制定
财务数据是企业运营的晴雨表，在车辆租赁行业也不例外。自建 Excel 多表统计交互能够将租赁合同表、财务收支表、车辆成本表等相关数据进行综合分析。通过多表统计，企业可以准确计算每笔租赁业务的利润，分析不同车型、不同租赁时长的盈利能力。例如，对比不同车型的购置成本、维修成本与租赁收入，评估哪种车型为企业带来的利润更高。此外，还能通过分析历史数据预测未来的财务趋势，如在特定季节或活动期间的收入变化，为企业制定预算、投资决策提供有力支持。例如，根据多表分析结果，企业可决定是否购置新车型以满足市场需求，或调整租赁价格策略以提高整体收益。
四、风险预警与防控，保障企业稳定运营
车辆租赁行业面临着诸如车辆损坏、逾期未还、客户违约等多种风险。自建 Excel 多表统计交互可通过整合租赁订单表、车辆状态表、客户信用表等数据，实现风险预警功能。例如，通过设置数据条件格式和函数公式，当车辆租赁即将到期时自动提醒工作人员，降低逾期未还风险；通过分析客户历史租赁记录和信用数据，对信用不佳的客户进行风险评估，在接单时谨慎处理，避免潜在的违约风险。同时，结合车辆状态表，对车辆的维修频率、故障情况进行统计分析，提前发现车辆潜在的安全隐患，及时安排维修保养，保障车辆的安全运营，维护企业的声誉和利益。
自建 Excel 实现多表统计交互在车辆租赁行业中发挥着不可或缺的重要作用。它从资源管理、客户服务、财务决策到风险防控等多个维度，为企业提供了全面、精准的数据支持和分析能力，帮助企业在激烈的市场竞争中脱颖而出，实现可持续发展。随着行业的不断发展和数据量的持续增长，不断优化和完善自建 Excel 多表统计交互功能，将成为车辆租赁企业提升核心竞争力的关键举措。
代码
<!DOCTYPE html>
<html lang="zh-CN">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>未来之窗EXCEL戴维斯跨表交互模拟</title>
<style>
/* 基础样式 */
* {
box-sizing: border-box;
margin: 0;
padding: 0;
font-family: Arial, sans-serif;
}
body {
background-color: #f5f7fa;
color: #1d2129;
line-height: 1.5;
min-height: 100vh;
display: flex;
flex-direction: column;
}
/* 布局组件 */
.container {
width: 100%;
max-width: 1200px;
margin: 0 auto;
padding: 0 20px;
}
/* 头部样式 */
header {
background-color: white;
box-shadow: 0 2px 4px rgba(0,0,0,0.1);
padding: 15px 0;
position: sticky;
top: 0;
z-index: 100;
}
.header-content {
display: flex;
justify-content: space-between;
align-items: center;
}
.logo {
font-size: 1.5rem;
font-weight: bold;
color: #165dff;
}
/* 主内容区 */
main {
flex: 1;
padding: 30px 0;
}
/* 步骤指示器 */
.steps {
display: flex;
justify-content: space-between;
max-width: 800px;
margin: 0 auto 40px;
position: relative;
}
.steps::before {
content: '';
position: absolute;
top: 20px;
left: 50px;
right: 50px;
height: 4px;
background-color: #e5e6eb;
z-index: 1;
}
.steps::after {
content: '';
position: absolute;
top: 20px;
left: 50px;
width: 33%;
height: 4px;
background-color: #165dff;
z-index: 2;
transition: width 0.5s ease;
}
.step {
display: flex;
flex-direction: column;
align-items: center;
position: relative;
z-index: 3;
}
.step-icon {
width: 40px;
height: 40px;
border-radius: 50%;
background-color: #165dff;
color: white;
display: flex;
align-items: center;
justify-content: center;
margin-bottom: 8px;
font-weight: bold;
}
.step:nth-child(3) .step-icon {
background-color: #e5e6eb;
color: #86909c;
}
.step-text {
font-size: 0.9rem;
font-weight: 500;
}
/* 网格布局 */
.grid-layout {
display: grid;
grid-template-columns: 1fr;
gap: 20px;
}
@media (min-width: 992px) {
.grid-layout {
grid-template-columns: 1fr 2fr;
}
}
/* 卡片组件 */
.card {
background-color: white;
border-radius: 6px;
box-shadow: 0 2px 8px rgba(0,0,0,0.08);
padding: 20px;
margin-bottom: 20px;
}
.card-title {
font-size: 1.1rem;
font-weight: 600;
margin-bottom: 15px;
color: #165dff;
display: flex;
align-items: center;
}
.card-title span {
margin-right: 8px;
}
/* 表单元素 */
.form-group {
margin-bottom: 15px;
}
label {
display: block;
margin-bottom: 6px;
font-size: 0.9rem;
color: #4e5969;
}
input, select, textarea {
width: 100%;
padding: 8px 12px;
border: 1px solid #dcdee2;
border-radius: 4px;
font-size: 0.9rem;
transition: border-color 0.2s;
}
input:focus, select:focus, textarea:focus {
outline: none;
border-color: #165dff;
box-shadow: 0 0 0 2px rgba(22, 93, 255, 0.1);
}
/* 按钮样式 */
.btn {
display: inline-flex;
align-items: center;
justify-content: center;
padding: 8px 16px;
background-color: #165dff;
color: white;
border: none;
border-radius: 4px;
font-size: 0.9rem;
font-weight: 500;
cursor: pointer;
transition: background-color 0.2s;
}
.btn:hover {
background-color: #0e47cc;
}
.btn-success {
background-color: #52c41a;
}
.btn-success:hover {
background-color: #43a616;
}
.btn-text {
background: none;
color: #165dff;
padding: 4px 8px;
}
.btn-text:hover {
background-color: rgba(22, 93, 255, 0.1);
color: #0e47cc;
}
/* 表选择样式 */
.table-option {
display: flex;
align-items: center;
padding: 10px;
border: 1px solid #dcdee2;
border-radius: 4px;
margin-bottom: 10px;
cursor: pointer;
transition: all 0.2s;
}
.table-option:hover {
border-color: #165dff;
background-color: rgba(22, 93, 255, 0.05);
}
.table-option input {
width: auto;
margin-right: 10px;
}
/* 字段配置样式 */
.field-item {
padding: 15px;
border: 1px solid #dcdee2;
border-radius: 4px;
background-color: #fafafa;
margin-bottom: 15px;
}
.field-row {
display: flex;
flex-wrap: wrap;
gap: 10px;
}
.field-col {
flex: 1;
min-width: 120px;
}
.remove-field {
background: none;
border: none;
color: #86909c;
cursor: pointer;
align-self: flex-end;
padding: 4px;
}
.remove-field:hover {
color: #ff4d4f;
}
.add-field-container {
display: flex;
justify-content: flex-end;
margin-bottom: 20px;
}
/* SQL预览样式 */
.sql-preview {
position: relative;
background-color: #fafafa;
border-radius: 4px;
padding: 15px;
font-family: monospace;
font-size: 0.9rem;
line-height: 1.5;
white-space: pre;
overflow-x: auto;
max-height: 300px;
margin-bottom: 15px;
}
.copy-btn {
position: absolute;
top: 10px;
right: 10px;
background-color: #f0f2f5;
border: none;
border-radius: 4px;
padding: 4px 8px;
cursor: pointer;
font-size: 0.8rem;
}
.copy-btn:hover {
background-color: #e5e6eb;
}
/* 表格样式 */
.result-table {
width: 100%;
border-collapse: collapse;
margin-bottom: 20px;
}
.result-table th,
.result-table td {
padding: 12px 15px;
text-align: left;
border-bottom: 1px solid #e5e6eb;
}
.result-table th {
background-color: #f5f7fa;
font-weight: 600;
color: #4e5969;
font-size: 0.85rem;
}
.result-table tr:hover {
background-color: #fafafa;
}
/* 分页样式 */
.pagination {
display: flex;
justify-content: space-between;
align-items: center;
margin-top: 20px;
font-size: 0.9rem;
}
.page-controls {
display: flex;
gap: 5px;
}
.page-btn {
padding: 5px 10px;
border: 1px solid #dcdee2;
border-radius: 4px;
background-color: white;
cursor: pointer;
}
.page-btn:hover {
border-color: #165dff;
color: #165dff;
}
.page-btn.active {
background-color: #165dff;
color: white;
border-color: #165dff;
}
.page-btn:disabled {
opacity: 0.5;
cursor: not-allowed;
}
/* 连接设置样式 */
.join-settings {
margin-top: 20px;
padding-top: 20px;
border-top: 1px solid #e5e6eb;
}
.toggle-container {
display: flex;
justify-content: space-between;
align-items: center;
margin-bottom: 15px;
}
.toggle-switch {
position: relative;
display: inline-block;
width: 40px;
height: 20px;
}
.toggle-switch input {
opacity: 0;
width: 0;
height: 0;
}
.toggle-slider {
position: absolute;
cursor: pointer;
top: 0;
left: 0;
right: 0;
bottom: 0;
background-color: #e5e6eb;
transition: .2s;
border-radius: 20px;
}
.toggle-slider:before {
position: absolute;
content: "";
height: 16px;
width: 16px;
left: 2px;
bottom: 2px;
background-color: white;
transition: .2s;
border-radius: 50%;
}
input:checked + .toggle-slider {
background-color: #165dff;
}
input:checked + .toggle-slider:before {
transform: translateX(20px);
}
/* 按钮组 */
.btn-group {
display: flex;
justify-content: flex-end;
gap: 10px;
margin-top: 20px;
}
/* 通知样式 */
.notification {
position: fixed;
top: 20px;
right: 20px;
background-color: #1d2129;
color: white;
padding: 10px 15px;
border-radius: 4px;
box-shadow: 0 2px 8px rgba(0,0,0,0.2);
transform: translateX(calc(100% + 20px));
transition: transform 0.3s ease;
z-index: 1000;
display: flex;
align-items: center;
}
.notification.show {
transform: translateX(0);
}
.notification span {
margin-left: 8px;
}
/* 页脚样式 */
footer {
background-color: white;
border-top: 1px solid #e5e6eb;
padding: 15px 0;
margin-top: 40px;
}
.footer-content {
text-align: center;
font-size: 0.85rem;
color: #86909c;
}
</style>
</head>
<body>
<!-- 顶部导航栏 -->
<header>
<div class="container header-content">
<div class="logo">未来之窗EXCEL戴维斯跨表交互模拟</div>
<div>
<button class="btn-text">帮助</button>
<button class="btn-text">设置</button>
</div>
</div>
</header>
<!-- 主要内容区 -->
<main>
<div class="container">
<!-- 步骤指示器 -->
<div class="steps">
<div class="step">
<div class="step-icon">1</div>
<div class="step-text">选择表</div>
</div>
<div class="step">
<div class="step-icon">2</div>
<div class="step-text">配置字段</div>
</div>
<div class="step">
<div class="step-icon">3</div>
<div class="step-text">查看结果</div>
</div>
</div>
<div class="grid-layout">
<!-- 左侧面板：表和字段选择 -->
<div>
<!-- 表选择卡片 -->
<div class="card">
<div class="card-title">
<span>1</span>选择数据表
</div>
<div>
<div class="table-option">
<input type="radio" name="table" value="table1" checked>
<span>东方仙盟小无相</span>
</div>
<div class="table-option">
<input type="radio" name="table" value="table2">
<span>项目信息表</span>
</div>
<div class="table-option">
<input type="radio" name="table" value="table3">
<span>客户信息表</span>
</div>
</div>
<div style="margin-top: 20px; padding-top: 20px; border-top: 1px solid #e5e6eb;">
<div class="form-group">
<label>表筛选条件 (table_sn)</label>
<input type="text" id="mainTableSn" value="779802252fb750aa016d20fc803b59ef052edec990002">
</div>
<button class="btn-text">+ 添加更多条件</button>
</div>
</div>
<!-- 字段配置卡片 -->
<div class="card">
<div style="display: flex; justify-content: space-between; align-items: center; margin-bottom: 15px;">
<div class="card-title">
<span>2</span>字段配置
</div>
<button id="addFieldBtn" class="btn">+ 添加字段</button>
</div>
<div id="fieldsContainer">
<!-- 字段配置项1 -->
<div class="field-item">
<div class="field-row">
<div class="field-col">
<label>字段</label>
<select class="field-select">
<option value="param1">param1</option>
<option value="param2">param2</option>
<option value="param11">param11</option>
<option value="param12">param12</option>
<option value="param13">param13</option>
<option value="param16" selected>param16</option>
</select>
</div>
<div class="field-col">
<label>显示名称</label>
<input type="text" value="工程名称" class="display-name">
</div>
<div class="field-col">
<label>处理方式</label>
<select class="process-type">
<option value="show" selected>显示</option>
<option value="sum">汇总(SUM)</option>
<option value="count">统计(COUNT)</option>
</select>
</div>
<div>
<button type="button" class="remove-field">×</button>
</div>
</div>
</div>
<!-- 字段配置项2 -->
<div class="field-item">
<div class="field-row">
<div class="field-col">
<label>字段</label>
<select class="field-select">
<option value="param1">param1</option>
<option value="param2" selected>param2</option>
<option value="param11">param11</option>
<option value="param12">param12</option>
<option value="param13">param13</option>
<option value="param16">param16</option>
</select>
</div>
<div class="field-col">
<label>显示名称</label>
<input type="text" value="结算单号" class="display-name">
</div>
<div class="field-col">
<label>处理方式</label>
<select class="process-type">
<option value="show" selected>显示</option>
<option value="sum">汇总(SUM)</option>
<option value="count">统计(COUNT)</option>
</select>
</div>
<div>
<button type="button" class="remove-field">×</button>
</div>
</div>
</div>
<!-- 字段配置项3 -->
<div class="field-item">
<div class="field-row">
<div class="field-col">
<label>字段</label>
<select class="field-select">
<option value="param1">param1</option>
<option value="param2">param2</option>
<option value="param11" selected>param11</option>
<option value="param12">param12</option>
<option value="param13">param13</option>
<option value="param16">param16</option>
</select>
</div>
<div class="field-col">
<label>显示名称</label>
<input type="text" value="结算金额" class="display-name">
</div>
<div class="field-col">
<label>处理方式</label>
<select class="process-type">
<option value="show">显示</option>
<option value="sum" selected>汇总(SUM)</option>
<option value="count">统计(COUNT)</option>
</select>
</div>
<div>
<button type="button" class="remove-field">×</button>
</div>
</div>
</div>
</div>
<!-- 多表连接配置 -->
<div class="join-settings">
<div class="toggle-container">
<div class="card-title" style="margin-bottom: 0;">多表连接</div>
<label class="toggle-switch">
<input type="checkbox" id="joinTables" checked>
<span class="toggle-slider"></span>
</label>
</div>
<div id="joinSettings">
<div class="form-group">
<label>关联表</label>
<select id="joinTableSelect">
<option value="table1">东方仙盟小无相</option>
<option value="table2">项目信息表</option>
<option value="table3">客户信息表</option>
</select>
</div>
<div class="form-group">
<label>关联表筛选条件 (table_sn)</label>
<input type="text" id="joinTableSn" value="77980225571289009447b68b5fb91dbdae9b200390002">
</div>
<div class="form-group">
<label>连接条件</label>
<div style="display: flex; gap: 10px;">
<select id="mainTableField" style="flex: 1;">
<option value="param16">主表: param16 (工程名称)</option>
<option value="param1">主表: param1</option>
<option value="param2">主表: param2</option>
</select>
<div style="display: flex; align-items: center;">=</div>
<select id="joinTableField" style="flex: 1;">
<option value="param1">关联表: param1 (工程名称)</option>
<option value="param2">关联表: param2</option>
<option value="param16">关联表: param16</option>
</select>
</div>
</div>
<div class="form-group">
<label>连接类型</label>
<select id="joinType">
<option value="JOIN">INNER JOIN (仅匹配记录)</option>
<option value="LEFT JOIN">LEFT JOIN (保留主表所有记录)</option>
<option value="RIGHT JOIN">RIGHT JOIN (保留关联表所有记录)</option>
</select>
</div>
</div>
</div>
<div class="btn-group">
<button id="generateSQLBtn" class="btn">生成SQL</button>
</div>
</div>
</div>
<!-- 右侧面板：SQL和结果 -->
<div>
<!-- SQL预览卡片 -->
<div class="card">
<div class="card-title">
<span>SQL</span>预览
</div>
<div class="sql-preview">
<button class="copy-btn" id="copySQLBtn">复制</button>
<div id="sqlPreview">SELECT
a.工程名称,
b.结算单号,
a.结算金额,
a.已付金额,
a.未付金额
FROM
(SELECT
param16 AS '工程名称',
SUM(CAST(param11 AS DECIMAL(10,2))) AS '结算金额',
SUM(CAST(param12 AS DECIMAL(10,2))) AS '已付金额',
SUM(CAST(param13 AS DECIMAL(10,2))) AS '未付金额'
FROM
东方仙盟小无相
WHERE
table_sn = '779802252fb750aa016d20fc803b59ef052edec990002'
GROUP BY
param16) AS a
JOIN
(SELECT
param1 AS '工程名称',
param2 AS '结算单号'
FROM
东方仙盟小无相
WHERE
table_sn = '77980225571289009447b68b5fb91dbdae9b200390002') AS b
ON
a.工程名称 = b.工程名称
ORDER BY
a.工程名称;</div>
</div>
<div class="btn-group">
<button id="executeSQLBtn" class="btn btn-success">执行查询</button>
</div>
</div>
<!-- 结果展示卡片 -->
<div class="card">
<div style="display: flex; justify-content: space-between; align-items: center; margin-bottom: 15px;">
<div class="card-title">
<span>3</span>查询结果
</div>
<div style="display: flex; gap: 10px;">
<button class="btn-text">导出</button>
<button class="btn-text">刷新</button>
</div>
</div>
<div style="overflow-x: auto;">
<table class="result-table">
<thead>
<tr>
<th>工程名称</th>
<th>结算单号</th>
<th>结算金额</th>
<th>已付金额</th>
<th>未付金额</th>
</tr>
</thead>
<tbody>
<tr>
<td>办公楼项目</td>
<td>JS2023001</td>
<td>1,250,000.00</td>
<td>750,000.00</td>
<td>500,000.00</td>
</tr>
<tr>
<td>住宅项目A区</td>
<td>JS2023002</td>
<td>3,800,000.00</td>
<td>1,900,000.00</td>
<td>1,900,000.00</td>
</tr>
<tr>
<td>商业综合体</td>
<td>JS2023003</td>
<td>8,500,000.00</td>
<td>5,100,000.00</td>
<td>3,400,000.00</td>
</tr>
</tbody>
</table>
</div>
<div class="pagination">
<div>显示 1 至 3，共 3 条记录</div>
<div class="page-controls">
<button class="page-btn" disabled>←</button>
<button class="page-btn active">1</button>
<button class="page-btn">2</button>
<button class="page-btn">→</button>
</div>
</div>
</div>
</div>
</div>
</div>
</main>
<!-- 页脚 -->
<footer>
<div class="container footer-content">
<p>© 2023 未来之窗EXCEL戴维斯跨表交互模拟</p>
</div>
</footer>
<!-- 通知提示框 -->
<div class="notification" id="notification">
<span id="notificationText">操作成功</span>
</div>
<script>
// 字段模板
const fieldTemplate = `
<div class="field-item">
<div class="field-row">
<div class="field-col">
<label>字段</label>
<select class="field-select">
<option value="param1">param1</option>
<option value="param2">param2</option>
<option value="param11">param11</option>
<option value="param12">param12</option>
<option value="param13">param13</option>
<option value="param16">param16</option>
</select>
</div>
<div class="field-col">
<label>显示名称</label>
<input type="text" value="" placeholder="请输入显示名称" class="display-name">
</div>
<div class="field-col">
<label>处理方式</label>
<select class="process-type">
<option value="show" selected>显示</option>
<option value="sum">汇总(SUM)</option>
<option value="count">统计(COUNT)</option>
</select>
</div>
<div>
<button type="button" class="remove-field">×</button>
</div>
</div>
</div>
`;
// 获取主表名称
function getMainTableName() {
const tableRadios = document.querySelectorAll('input[name="table"]');
for (const radio of tableRadios) {
if (radio.checked) {
if (radio.value === 'table1') return '东方仙盟小无相';
if (radio.value === 'table2') return '项目信息表';
if (radio.value === 'table3') return '客户信息表';
}
}
return '东方仙盟小无相';
}
// 获取关联表名称
function getJoinTableName() {
const joinTableSelect = document.getElementById('joinTableSelect');
if (joinTableSelect.value === 'table1') return '东方仙盟小无相';
if (joinTableSelect.value === 'table2') return '项目信息表';
if (joinTableSelect.value === 'table3') return '客户信息表';
return '东方仙盟小无相';
}
// 生成SQL
function generateSQL() {
// 获取主表信息
const mainTableName = getMainTableName();
const mainTableSn = document.getElementById('mainTableSn').value;
// 获取关联表信息
const useJoin = document.getElementById('joinTables').checked;
let joinTableName = '';
let joinTableSn = '';
let joinType = '';
let mainField = '';
let joinField = '';
if (useJoin) {
joinTableName = getJoinTableName();
joinTableSn = document.getElementById('joinTableSn').value;
joinType = document.getElementById('joinType').value;
mainField = document.getElementById('mainTableField').value;
joinField = document.getElementById('joinTableField').value;
// 提取字段名（去掉显示文本）
mainField = mainField.split(':')[1].trim().split(' ')[0];
joinField = joinField.split(':')[1].trim().split(' ')[0];
}
// 获取所有字段配置
const fieldItems = document.querySelectorAll('.field-item');
const mainTableFields = [];
const joinTableFields = [];
const selectFields = [];
// 简单区分主表和关联表字段（实际应用中需要更复杂的逻辑）
const mainTableFieldNames = ['param16', 'param11', 'param12', 'param13'];
const joinTableFieldNames = ['param1', 'param2'];
fieldItems.forEach((item, index) => {
const fieldSelect = item.querySelector('.field-select');
const displayName = item.querySelector('.display-name').value;
const processType = item.querySelector('.process-type').value;
const fieldName = fieldSelect.value;
// 构建选择字段
let selectField;
if (processType === 'show') {
selectField = `${fieldName} AS '${displayName}'`;
} else if (processType === 'sum') {
selectField = `SUM(CAST(${fieldName} AS DECIMAL(10,2))) AS '${displayName}'`;
} else { // count
selectField = `COUNT(${fieldName}) AS '${displayName}'`;
}
// 分配到对应的表
if (mainTableFieldNames.includes(fieldName)) {
mainTableFields.push(selectField);
selectFields.push(`a.${displayName}`);
} else if (joinTableFieldNames.includes(fieldName)) {
joinTableFields.push(selectField);
selectFields.push(`b.${displayName}`);
} else {
// 默认为主表字段
mainTableFields.push(selectField);
selectFields.push(`a.${displayName}`);
}
});
// 构建主表子查询
let mainSubquery = `(SELECT
${mainTableFields.join(',\n ')}
FROM
${mainTableName}
WHERE
table_sn = '${mainTableSn}'`;
// 确定需要GROUP BY的字段
const groupByFields = [];
fieldItems.forEach(item => {
const fieldName = item.querySelector('.field-select').value;
const processType = item.querySelector('.process-type').value;
if (processType === 'show' && mainTableFieldNames.includes(fieldName)) {
groupByFields.push(fieldName);
}
});
if (groupByFields.length > 0) {
mainSubquery += `\n GROUP BY
${groupByFields.join(', ')}`;
}
mainSubquery += ') AS a';
// 构建关联表子查询
let joinSubquery = '';
if (useJoin && joinTableFields.length > 0) {
joinSubquery = `${joinType}
(SELECT
${joinTableFields.join(',\n ')}
FROM
${joinTableName}
WHERE
table_sn = '${joinTableSn}') AS b
ON
a.${mainField.split('.').pop()} = b.${joinField.split('.').pop()}`;
}
// 构建完整SQL
let sql = `SELECT
${selectFields.join(',\n ')}
FROM
${mainSubquery}
${useJoin && joinTableFields.length > 0 ? joinSubquery : ''}
${groupByFields.length > 0 ? `\nORDER BY
a.${groupByFields[0]}` : ''};`;
// 格式化SQL
sql = sql.replace(/ /g, ' ');
return sql;
}
// 添加字段按钮事件
document.getElementById('addFieldBtn').addEventListener('click', function() {
const container = document.getElementById('fieldsContainer');
const div = document.createElement('div');
div.innerHTML = fieldTemplate;
container.appendChild(div.firstElementChild);
// 为新添加的字段绑定删除事件
bindRemoveEvents();
// 显示通知
showNotification('字段已添加');
});
// 绑定删除字段事件
function bindRemoveEvents() {
document.querySelectorAll('.remove-field').forEach(button => {
button.addEventListener('click', function() {
const fieldItem = this.closest('.field-item');
fieldItem.style.opacity = '0';
fieldItem.style.transform = 'scale(0.95)';
fieldItem.style.transition = 'all 0.2s ease';
setTimeout(() => {
fieldItem.remove();
showNotification('字段已删除');
}, 200);
});
});
}
// 生成SQL按钮事件
document.getElementById('generateSQLBtn').addEventListener('click', function() {
// 显示加载状态
this.textContent = '生成中...';
this.disabled = true;
setTimeout(() => {
// 生成SQL
const sql = generateSQL();
document.getElementById('sqlPreview').textContent = sql;
// 恢复按钮状态
this.textContent = '生成SQL';
this.disabled = false;
// 显示通知
showNotification('SQL已生成');
// 更新步骤指示器
document.querySelector('.steps::after').style.width = '100%';
}, 800);
});
// 执行SQL按钮事件
document.getElementById('executeSQLBtn').addEventListener('click', function() {
// 显示加载状态
this.textContent = '执行中...';
this.disabled = true;
setTimeout(() => {
// 恢复按钮状态
this.textContent = '执行查询';
this.disabled = false;
// 模拟执行完成，显示通知
showNotification('查询已执行，共返回3条记录');
// 更新步骤指示器，激活第三步
document.querySelector('.step:nth-child(3) .step-icon').style.backgroundColor = '#165dff';
document.querySelector('.step:nth-child(3) .step-icon').style.color = 'white';
}, 1200);
});
// 复制SQL按钮事件
document.getElementById('copySQLBtn').addEventListener('click', function() {
const sqlText = document.getElementById('sqlPreview').textContent;
const textarea = document.createElement('textarea');
textarea.value = sqlText;
document.body.appendChild(textarea);
textarea.select();
document.execCommand('copy');
document.body.removeChild(textarea);
showNotification('SQL已复制到剪贴板');
});
// 多表连接开关事件
document.getElementById('joinTables').addEventListener('change', function() {
const joinSettings = document.getElementById('joinSettings');
if (this.checked) {
joinSettings.style.maxHeight = '500px';
joinSettings.style.opacity = '1';
joinSettings.style.overflow = 'visible';
} else {
joinSettings.style.maxHeight = '0';
joinSettings.style.opacity = '0';
joinSettings.style.overflow = 'hidden';
joinSettings.style.transition = 'max-height 0.3s ease, opacity 0.3s ease';
}
});
// 表选择变更时更新SQL
document.querySelectorAll('input[name="table"]').forEach(radio => {
radio.addEventListener('change', function() {
// 当表选择变更时，可以自动更新SQL或提示用户重新生成
});
});
// 字段变更时更新SQL
document.addEventListener('change', function(e) {
if (e.target.classList.contains('field-select') ||
e.target.classList.contains('display-name') ||
e.target.classList.contains('process-type')) {
// 字段配置变更，可以延迟生成SQL
}
});
// 显示通知函数
function showNotification(message) {
const notification = document.getElementById('notification');
const notificationText = document.getElementById('notificationText');
notificationText.textContent = message;
notification.classList.add('show');
setTimeout(() => {
notification.classList.remove('show');
}, 3000);
}
// 初始化事件绑定
document.addEventListener('DOMContentLoaded', function() {
bindRemoveEvents();
});
</script>
</body>
</html>
阿雪技术观
在科技发展浪潮中，我们不妨积极投身技术共享。不满足于做受益者，更要主动担当贡献者。无论是分享代码、撰写技术博客，还是参与开源项目维护改进，每一个微小举动都可能蕴含推动技术进步的巨大能量。东方仙盟是汇聚力量的天地，我们携手在此探索硅基生命，为科技进步添砖加瓦。
Hey folks, in this wild tech - driven world, why not dive headfirst into the whole tech - sharing scene? Don't just be the one reaping all the benefits; step up and be a contributor too. Whether you're tossing out your code snippets, hammering out some tech blogs, or getting your hands dirty with maintaining and sprucing up open - source projects, every little thing you do might just end up being a massive force that pushes tech forward. And guess what? The Eastern FairyAlliance is this awesome place where we all come together. We're gonna team up and explore the whole silicon - based life thing, and in the process, we'll be fueling the growth of technology