表关联映射工具

于 2025-05-11 10:42:06 发布
阅读量373 收藏
点赞数 9
分类专栏： 大模型 AI编程 文章标签： 字段映射 值映射 SQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wxl781227/article/details/147869576
版权

坐席业绩场景通常存在数据分散在不同的系统情况，字段可以关联的场景可以直接使用字段关联，字段无法关联的场景需要进行值关联，典型的情况：坐席ID不变，这个月张三在打电话，下个月张三离职了，是李四在打电话，但坐席ID没有变，人换了，这个时候就需要使用值关联来实现业绩的统计。

下面是字段关联和值关联的实现的例子，提供了优化易操作的界面，在进行字段关联和值关联的同时还可以自动生成SQL，供统计查询使用。

假设B是坐席辅助系统的表，A是从其它系统导出的数据表，通常以CSV格式存在，导入到坐席辅助系统后，表名为A。

下面是基于拖动操作并字段生成SQL的例子代码：

<!DOCTYPE html>
<html lang="zh-CN">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>表关联映射工具</title>
  <script src="https://cdn.tailwindcss.com"></script>
  <link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.7.2/css/all.min.css" rel="stylesheet">
  <script>
    tailwind.config = {
      theme: {
        extend: {
          colors: {
            primary: '#3b82f6',
            secondary: '#64748b',
            success: '#10b981',
            warning: '#f59e0b',
            danger: '#ef4444',
            info: '#06b6d4',
            light: '#f8fafc',
            dark: '#1e293b'
          },
          fontFamily: {
            inter: ['Inter', 'sans-serif'],
          },
        },
      }
    }
  </script>
  <style type="text/tailwindcss">
    @layer utilities {
      .content-auto {
        content-visibility: auto;
      }
      .dragging {
        @apply opacity-50 scale-95 cursor-grabbing;
      }
      .drop-zone {
        @apply border-2 border-dashed border-primary/50 bg-primary/5;
      }
      .connection-line {
        @apply fixed pointer-events-none z-10;
      }
      .transition-all-300 {
        @apply transition-all duration-300 ease-in-out;
      }
      .hover-scale {
        @apply hover:scale-105 transition-all duration-200;
      }
      .card-shadow {
        @apply shadow-lg hover:shadow-xl transition-all duration-300;
      }
      .sql-container {
        @apply relative;
      }
      .sql-copy-btn {
        @apply absolute top-2 right-2 bg-primary/10 text-primary px-2 py-1 rounded text-sm hover:bg-primary/20 transition-all-300;
      }
    }
  </style>
</head>
<body class="bg-gray-50 font-inter text-gray-800 min-h-screen">
  <!-- 顶部导航栏 -->
  <header class="bg-white shadow-md sticky top-0 z-50">
    <div class="container mx-auto px-4 py-3 flex justify-between items-center">
      <div class="flex items-center space-x-2">
        <i class="fa fa-database text-primary text-2xl"></i>
        <h1 class="text-xl font-bold text-gray-800">表关联映射工具</h1>
      </div>
      <div class="flex items-center space-x-4">
        <button class="px-4 py-2 bg-primary text-white rounded-lg hover:bg-primary/90 transition-all-300 flex items-center">
          <i class="fa fa-save mr-2"></i>保存映射
        </button>
        <button class="px-4 py-2 bg-gray-200 text-gray-700 rounded-lg hover:bg-gray-300 transition-all-300 flex items-center">
          <i class="fa fa-question-circle mr-2"></i>帮助
        </button>
      </div>
    </div>
  </header>

  <main class="container mx-auto px-4 py-8">
    <!-- 映射状态指示器 -->
    <div class="mb-8 bg-white rounded-xl p-4 shadow-md">
      <div class="flex items-center justify-between">
        <div class="flex items-center space-x-4">
          <div class="flex items-center">
            <div class="w-3 h-3 rounded-full bg-success mr-2"></div>
            <span>字段映射: <span class="font-semibold">3/5</span></span>
          </div>
          <div class="flex items-center">
            <div class="w-3 h-3 rounded-full bg-warning mr-2"></div>
            <span>值映射: <span class="font-semibold">2/4</span></span>
          </div>
        </div>
        <div class="flex space-x-3">
          <button class="px-3 py-1.5 bg-primary/10 text-primary rounded-lg hover:bg-primary/20 transition-all-300">
            <i class="fa fa-refresh mr-1"></i>重置映射
          </button>
          <button class="px-3 py-1.5 bg-gray-200 text-gray-700 rounded-lg hover:bg-gray-300 transition-all-300">
            <i class="fa fa-code mr-1"></i>导出配置
          </button>
        </div>
      </div>
      <div class="w-full bg-gray-200 rounded-full h-2.5 mt-3">
        <div class="bg-gradient-to-r from-primary to-success h-2.5 rounded-full" style="width: 50%"></div>
      </div>
    </div>

    <div class="grid grid-cols-1 lg:grid-cols-2 gap-8">
      <!-- 表A区域 -->
      <div class="bg-white rounded-xl overflow-hidden shadow-lg card-shadow">
        <div class="bg-primary text-white p-4 flex justify-between items-center">
          <h2 class="text-lg font-bold flex items-center">
            <i class="fa fa-table mr-2"></i>表 A
          </h2>
          <div class="flex space-x-2">
            <button class="p-1.5 rounded hover:bg-white/20 transition-all-300" title="刷新">
              <i class="fa fa-refresh"></i>
            </button>
            <button class="p-1.5 rounded hover:bg-white/20 transition-all-300" title="设置">
              <i class="fa fa-cog"></i>
            </button>
          </div>
        </div>
        
        <!-- 表A字段 -->
        <div class="p-4 border-b border-gray-200">
          <h3 class="font-semibold mb-3 flex items-center">
            <i class="fa fa-list-ul text-primary mr-2"></i>字段列表
          </h3>
          <div id="table-a-fields" class="grid grid-cols-1 sm:grid-cols-2 gap-2">
            <div class="field-item bg-gray-100 p-3 rounded-lg cursor-move hover-scale flex justify-between items-center" draggable="true" data-field="id">
              <div class="flex items-center">
                <i class="fa fa-arrows-alt text-gray-400 mr-2"></i>
                <span class="font-medium">ID</span>
              </div>
              <span class="text-xs text-gray-500">int</span>
            </div>
            <div class="field-item bg-gray-100 p-3 rounded-lg cursor-move hover-scale flex justify-between items-center" draggable="true" data-field="name">
              <div class="flex items-center">
                <i class="fa fa-arrows-alt text-gray-400 mr-2"></i>
                <span class="font-medium">姓名</span>
              </div>
              <span class="text-xs text-gray-500">varchar</span>
            </div>
            <div class="field-item bg-gray-100 p-3 rounded-lg cursor-move hover-scale flex justify-between items-center" draggable="true" data-field="age">
              <div class="flex items-center">
                <i class="fa fa-arrows-alt text-gray-400 mr-2"></i>
                <span class="font-medium">年龄</span>
              </div>
              <span class="text-xs text-gray-500">int</span>
            </div>
            <div class="field-item bg-gray-100 p-3 rounded-lg cursor-move hover-scale flex justify-between items-center" draggable="true" data-field="gender">
              <div class="flex items-center">
                <i class="fa fa-arrows-alt text-gray-400 mr-2"></i>
                <span class="font-medium">性别</span>
              </div>
              <span class="text-xs text-gray-500">varchar</span>
            </div>
            <div class="field-item bg-gray-100 p-3 rounded-lg cursor-move hover-scale flex justify-between items-center" draggable="true" data-field="email">
              <div class="flex items-center">
                <i class="fa fa-arrows-alt text-gray-400 mr-2"></i>
                <span class="font-medium">邮箱</span>
              </div>
              <span class="text-xs text-gray-500">varchar</span>
            </div>
          </div>
        </div>
        
        <!-- 表A数据 -->
        <div class="p-4">
          <h3 class="font-semibold mb-3 flex items-center">
            <i class="fa fa-database text-primary mr-2"></i>数据示例
          </h3>
          <div id="table-a-data" class="overflow-x-auto">
            <table class="min-w-full divide-y divide-gray-200">
              <thead class="bg-gray-50">
                <tr>
                  <th scope="col" class="px-3 py-2 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">ID</th>
                  <th scope="col" class="px-3 py-2 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">姓名</th>
                  <th scope="col" class="px-3 py-2 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">年龄</th>
                  <th scope="col" class="px-3 py-2 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">性别</th>
                  <th scope="col" class="px-3 py-2 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">邮箱</th>
                </tr>
              </thead>
              <tbody class="bg-white divide-y divide-gray-200">
                <tr class="value-row hover:bg-gray-50">
                  <td class="px-3 py-2 whitespace-nowrap text-sm font-medium text-gray-900 value-item" draggable="true" data-value="1" data-field="id">1</td>
                  <td class="px-3 py-2 whitespace-nowrap text-sm text-gray-500 value-item" draggable="true" data-value="张三" data-field="name">张三</td>
                  <td class="px-3 py-2 whitespace-nowrap text-sm text-gray-500 value-item" draggable="true" data-value="25" data-field="age">25</td>
                  <td class="px-3 py-2 whitespace-nowrap text-sm text-gray-500 value-item" draggable="true" data-value="男" data-field="gender">男</td>
                  <td class="px-3 py-2 whitespace-nowrap text-sm text-gray-500 value-item" draggable="true" data-value="zhangsan@example.com" data-field="email">zhangsan@example.com</td>
                </tr>
                <tr class="value-row hover:bg-gray-50">
                  <td class="px-3 py-2 whitespace-nowrap text-sm font-medium text-gray-900 value-item" draggable="true" data-value="2" data-field="id">2</td>
                  <td class="px-3 py-2 whitespace-nowrap text-sm text-gray-500 value-item" draggable="true" data-value="李四" data-field="name">李四</td>
                  <td class="px-3 py-2 whitespace-nowrap text-sm text-gray-500 value-item" draggable="true" data-value="30" data-field="age">30</td>
                  <td class="px-3 py-2 whitespace-nowrap text-sm text-gray-500 value-item" draggable="true" data-value="女" data-field="gender">女</td>
                  <td class="px-3 py-2 whitespace-nowrap text-sm text-gray-500 value-item" draggable="true" data-value="lisi@example.com" data-field="email">lisi@example.com</td>
                </tr>
              </tbody>
            </table>
          </div>
        </div>
      </div>

      <!-- 表B区域 -->
      <div class="bg-white rounded-xl overflow-hidden shadow-lg card-shadow">
        <div class="bg-info text-white p-4 flex justify-between items-center">
          <h2 class="text-lg font-bold flex items-center">
            <i class="fa fa-table mr-2"></i>表 B
          </h2>
          <div class="flex space-x-2">
            <button class="p-1.5 rounded hover:bg-white/20 transition-all-300" title="刷新">
              <i class="fa fa-refresh"></i>
            </button>
            <button class="p-1.5 rounded hover:bg-white/20 transition-all-300" title="设置">
              <i class="fa fa-cog"></i>
            </button>
          </div>
        </div>
        
        <!-- 表B字段 -->
        <div class="p-4 border-b border-gray-200">
          <h3 class="font-semibold mb-3 flex items-center">
            <i class="fa fa-list-ul text-info mr-2"></i>字段列表
          </h3>
          <div id="table-b-fields" class="grid grid-cols-1 sm:grid-cols-2 gap-2">
            <div class="field-item bg-gray-100 p-3 rounded-lg cursor-pointer hover-scale flex justify-between items-center drop-zone" data-field="user_id">
              <div class="flex items-center">
                <i class="fa fa-link text-gray-400 mr-2"></i>
                <span class="font-medium">USER_ID</span>
              </div>
              <span class="text-xs text-gray-500">int</span>
            </div>
            <div class="field-item bg-gray-100 p-3 rounded-lg cursor-pointer hover-scale flex justify-between items-center drop-zone" data-field="full_name">
              <div class="flex items-center">
                <i class="fa fa-link text-gray-400 mr-2"></i>
                <span class="font-medium">FULL_NAME</span>
              </div>
              <span class="text-xs text-gray-500">varchar</span>
            </div>
            <div class="field-item bg-gray-100 p-3 rounded-lg cursor-pointer hover-scale flex justify-between items-center drop-zone" data-field="years_old">
              <div class="flex items-center">
                <i class="fa fa-link text-gray-400 mr-2"></i>
                <span class="font-medium">YEARS_OLD</span>
              </div>
              <span class="text-xs text-gray-500">int</span>
            </div>
            <div class="field-item bg-gray-100 p-3 rounded-lg cursor-pointer hover-scale flex justify-between items-center drop-zone" data-field="user_gender">
              <div class="flex items-center">
                <i class="fa fa-link text-gray-400 mr-2"></i>
                <span class="font-medium">USER_GENDER</span>
              </div>
              <span class="text-xs text-gray-500">varchar</span>
            </div>
            <div class="field-item bg-gray-100 p-3 rounded-lg cursor-pointer hover-scale flex justify-between items-center drop-zone" data-field="user_email">
              <div class="flex items-center">
                <i class="fa fa-link text-gray-400 mr-2"></i>
                <span class="font-medium">USER_EMAIL</span>
              </div>
              <span class="text-xs text-gray-500">varchar</span>
            </div>
          </div>
        </div>
        
        <!-- 表B数据 -->
        <div class="p-4">
          <h3 class="font-semibold mb-3 flex items-center">
            <i class="fa fa-database text-info mr-2"></i>数据示例
          </h3>
          <div id="table-b-data" class="overflow-x-auto">
            <table class="min-w-full divide-y divide-gray-200">
              <thead class="bg-gray-50">
                <tr>
                  <th scope="col" class="px-3 py-2 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">USER_ID</th>
                  <th scope="col" class="px-3 py-2 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">FULL_NAME</th>
                  <th scope="col" class="px-3 py-2 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">YEARS_OLD</th>
                  <th scope="col" class="px-3 py-2 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">USER_GENDER</th>
                  <th scope="col" class="px-3 py-2 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">USER_EMAIL</th>
                </tr>
              </thead>
              <tbody class="bg-white divide-y divide-gray-200">
                <tr class="value-row hover:bg-gray-50">
                  <td class="px-3 py-2 whitespace-nowrap text-sm font-medium text-gray-900 drop-zone" data-value="101" data-field="user_id">101</td>
                  <td class="px-3 py-2 whitespace-nowrap text-sm text-gray-500 drop-zone" data-value="Zhang San" data-field="full_name">Zhang San</td>
                  <td class="px-3 py-2 whitespace-nowrap text-sm text-gray-500 drop-zone" data-value="25" data-field="years_old">25</td>
                  <td class="px-3 py-2 whitespace-nowrap text-sm text-gray-500 drop-zone" data-value="Male" data-field="user_gender">Male</td>
                  <td class="px-3 py-2 whitespace-nowrap text-sm text-gray-500 drop-zone" data-value="zhangsan@example.com" data-field="user_email">zhangsan@example.com</td>
                </tr>
                <tr class="value-row hover:bg-gray-50">
                  <td class="px-3 py-2 whitespace-nowrap text-sm font-medium text-gray-900 drop-zone" data-value="102" data-field="user_id">102</td>
                  <td class="px-3 py-2 whitespace-nowrap text-sm text-gray-500 drop-zone" data-value="Li Si" data-field="full_name">Li Si</td>
                  <td class="px-3 py-2 whitespace-nowrap text-sm text-gray-500 drop-zone" data-value="30" data-field="years_old">30</td>
                  <td class="px-3 py-2 whitespace-nowrap text-sm text-gray-500 drop-zone" data-value="Female" data-field="user_gender">Female</td>
                  <td class="px-3 py-2 whitespace-nowrap text-sm text-gray-500 drop-zone" data-value="lisi@example.com" data-field="user_email">lisi@example.com</td>
                </tr>
              </tbody>
            </table>
          </div>
        </div>
      </div>
    </div>

    <!-- 映射关系展示 -->
    <div class="mt-8 bg-white rounded-xl overflow-hidden shadow-lg card-shadow">
      <div class="bg-secondary text-white p-4 flex justify-between items-center">
        <h2 class="text-lg font-bold flex items-center">
          <i class="fa fa-sitemap mr-2"></i>映射关系
        </h2>
        <div class="flex space-x-2">
          <button class="p-1.5 rounded hover:bg-white/20 transition-all-300" title="刷新">
            <i class="fa fa-refresh"></i>
          </button>
          <button class="p-1.5 rounded hover:bg-white/20 transition-all-300" title="导出">
            <i class="fa fa-download"></i>
          </button>
        </div>
      </div>
      
      <div class="p-4">
        <div class="grid grid-cols-1 md:grid-cols-2 gap-6">
          <!-- 字段映射 -->
          <div>
            <h3 class="font-semibold mb-3 flex items-center text-primary">
              <i class="fa fa-link mr-2"></i>字段映射
            </h3>
            <div id="field-mappings" class="space-y-2">
              <div class="bg-primary/5 p-3 rounded-lg flex justify-between items-center">
                <div class="flex items-center">
                  <span class="bg-primary/10 text-primary px-2 py-1 rounded text-sm font-medium mr-2">ID</span>
                  <i class="fa fa-long-arrow-right text-gray-400 mx-2"></i>
                  <span class="bg-info/10 text-info px-2 py-1 rounded text-sm font-medium">USER_ID</span>
                </div>
                <button class="text-gray-400 hover:text-danger transition-all-300">
                  <i class="fa fa-times"></i>
                </button>
              </div>
              <div class="bg-primary/5 p-3 rounded-lg flex justify-between items-center">
                <div class="flex items-center">
                  <span class="bg-primary/10 text-primary px-2 py-1 rounded text-sm font-medium mr-2">姓名</span>
                  <i class="fa fa-long-arrow-right text-gray-400 mx-2"></i>
                  <span class="bg-info/10 text-info px-2 py-1 rounded text-sm font-medium">FULL_NAME</span>
                </div>
                <button class="text-gray-400 hover:text-danger transition-all-300">
                  <i class="fa fa-times"></i>
                </button>
              </div>
              <div class="bg-primary/5 p-3 rounded-lg flex justify-between items-center">
                <div class="flex items-center">
                  <span class="bg-primary/10 text-primary px-2 py-1 rounded text-sm font-medium mr-2">年龄</span>
                  <i class="fa fa-long-arrow-right text-gray-400 mx-2"></i>
                  <span class="bg-info/10 text-info px-2 py-1 rounded text-sm font-medium">YEARS_OLD</span>
                </div>
                <button class="text-gray-400 hover:text-danger transition-all-300">
                  <i class="fa fa-times"></i>
                </button>
              </div>
            </div>
          </div>
          
          <!-- 值映射 -->
          <div>
            <h3 class="font-semibold mb-3 flex items-center text-info">
              <i class="fa fa-exchange mr-2"></i>值映射
            </h3>
            <div id="value-mappings" class="space-y-2">
              <div class="bg-info/5 p-3 rounded-lg flex justify-between items-center">
                <div class="flex items-center">
                  <span class="bg-primary/10 text-primary px-2 py-1 rounded text-sm font-medium mr-2">男</span>
                  <i class="fa fa-long-arrow-right text-gray-400 mx-2"></i>
                  <span class="bg-info/10 text-info px-2 py-1 rounded text-sm font-medium">Male</span>
                </div>
                <button class="text-gray-400 hover:text-danger transition-all-300">
                  <i class="fa fa-times"></i>
                </button>
              </div>
              <div class="bg-info/5 p-3 rounded-lg flex justify-between items-center">
                <div class="flex items-center">
                  <span class="bg-primary/10 text-primary px-2 py-1 rounded text-sm font-medium mr-2">女</span>
                  <i class="fa fa-long-arrow-right text-gray-400 mx-2"></i>
                  <span class="bg-info/10 text-info px-2 py-1 rounded text-sm font-medium">Female</span>
                </div>
                <button class="text-gray-400 hover:text-danger transition-all-300">
                  <i class="fa fa-times"></i>
                </button>
              </div>
              <div class="bg-info/5 p-3 rounded-lg flex justify-between items-center">
                <div class="flex items-center">
                  <span class="bg-primary/10 text-primary px-2 py-1 rounded text-sm font-medium mr-2">张三</span>
                  <i class="fa fa-long-arrow-right text-gray-400 mx-2"></i>
                  <span class="bg-info/10 text-info px-2 py-1 rounded text-sm font-medium">Zhang San</span>
                </div>
                <button class="text-gray-400 hover:text-danger transition-all-300">
                  <i class="fa fa-times"></i>
                </button>
              </div>
              <div class="bg-info/5 p-3 rounded-lg flex justify-between items-center">
                <div class="flex items-center">
                  <span class="bg-primary/10 text-primary px-2 py-1 rounded text-sm font-medium mr-2">李四</span>
                  <i class="fa fa-long-arrow-right text-gray-400 mx-2"></i>
                  <span class="bg-info/10 text-info px-2 py-1 rounded text-sm font-medium">Li Si</span>
                </div>
                <button class="text-gray-400 hover:text-danger transition-all-300">
                  <i class="fa fa-times"></i>
                </button>
              </div>
            </div>
          </div>
        </div>
      </div>
    </div>

    <!-- SQL 生成区域 -->
    <div class="mt-8 bg-white rounded-xl overflow-hidden shadow-lg card-shadow">
      <div class="bg-success text-white p-4 flex justify-between items-center">
        <h2 class="text-lg font-bold flex items-center">
          <i class="fa fa-code mr-2"></i>生成的 SQL
        </h2>
        <div class="flex space-x-2">
          <button id="copy-sql-btn" class="p-1.5 rounded hover:bg-white/20 transition-all-300 flex items-center">
            <i class="fa fa-copy mr-1"></i>复制
          </button>
          <button class="p-1.5 rounded hover:bg-white/20 transition-all-300" title="下载">
            <i class="fa fa-download"></i>
          </button>
        </div>
      </div>
      
      <div class="p-4">
        <div class="mb-4 flex items-center text-sm text-gray-600">
          <i class="fa fa-info-circle text-info mr-2"></i>
          <span>基于当前映射关系生成的 SQL 语句。可根据需要进行调整。</span>
        </div>
        
        <div class="sql-container">
          <button class="sql-copy-btn" id="copy-select-sql">
            <i class="fa fa-copy mr-1"></i>复制 SELECT
          </button>
          <pre class="bg-gray-800 text-green-400 p-4 rounded-lg overflow-x-auto"><code id="select-sql">SELECT
    a.id AS user_id,
    CASE a.姓名
        WHEN '张三' THEN 'Zhang San'
        WHEN '李四' THEN 'Li Si'
        ELSE a.姓名
    END AS full_name,
    a.年龄 AS years_old,
    CASE a.性别
        WHEN '男' THEN 'Male'
        WHEN '女' THEN 'Female'
        ELSE a.性别
    END AS user_gender,
    a.邮箱 AS user_email
FROM
    表A a
LEFT JOIN
    表B b ON a.id = b.user_id;</code></pre>
        </div>
        
        <div class="sql-container mt-4">
          <button class="sql-copy-btn" id="copy-insert-sql">
            <i class="fa fa-copy mr-1"></i>复制 INSERT
          </button>
          <pre class="bg-gray-800 text-green-400 p-4 rounded-lg overflow-x-auto"><code id="insert-sql">INSERT INTO 表B (user_id, full_name, years_old, user_gender, user_email)
SELECT
    a.id,
    CASE a.姓名
        WHEN '张三' THEN 'Zhang San'
        WHEN '李四' THEN 'Li Si'
        ELSE a.姓名
    END,
    a.年龄,
    CASE a.性别
        WHEN '男' THEN 'Male'
        WHEN '女' THEN 'Female'
        ELSE a.性别
    END,
    a.邮箱
FROM
    表A a
WHERE
    NOT EXISTS (SELECT 1 FROM 表B b WHERE b.user_id = a.id);</code></pre>
        </div>
        
        <div class="sql-container mt-4">
          <button class="sql-copy-btn" id="copy-update-sql">
            <i class="fa fa-copy mr-1"></i>复制 UPDATE
          </button>
          <pre class="bg-gray-800 text-green-400 p-4 rounded-lg overflow-x-auto"><code id="update-sql">UPDATE 表B b
SET
    b.full_name = CASE a.姓名
        WHEN '张三' THEN 'Zhang San'
        WHEN '李四' THEN 'Li Si'
        ELSE a.姓名
    END,
    b.years_old = a.年龄,
    b.user_gender = CASE a.性别
        WHEN '男' THEN 'Male'
        WHEN '女' THEN 'Female'
        ELSE a.性别
    END,
    b.user_email = a.邮箱
FROM
    表A a
WHERE
    b.user_id = a.id;</code></pre>
        </div>
      </div>
    </div>
  </main>

  <footer class="bg-gray-800 text-white py-6 mt-8">
    <div class="container mx-auto px-4">
      <div class="flex flex-col md:flex-row justify-between items-center">
        <div class="mb-4 md:mb-0">
          <div class="flex items-center space-x-2">
            <i class="fa fa-database text-primary text-xl"></i>
            <span class="font-bold text-lg">表关联映射工具</span>
          </div>
          <p class="text-gray-400 text-sm mt-1">高效完成数据表字段与值的映射配置</p>
        </div>
        <div class="flex space-x-6">
          <a href="#" class="text-gray-400 hover:text-white transition-all-300">
            <i class="fa fa-question-circle"></i> 帮助
          </a>
          <a href="#" class="text-gray-400 hover:text-white transition-all-300">
            <i class="fa fa-book"></i> 文档
          </a>
          <a href="#" class="text-gray-400 hover:text-white transition-all-300">
            <i class="fa fa-github"></i> GitHub
          </a>
        </div>
      </div>
      <div class="border-t border-gray-700 mt-4 pt-4 text-center text-gray-400 text-sm">
        © 2025 表关联映射工具. 保留所有权利.
      </div>
    </div>
  </footer>

  <script>
    // 存储映射关系
    const fieldMappings = new Map();
    const valueMappings = new Map();
    const fieldValueMap = new Map(); // 跟踪值映射属于哪个字段
    
    // 存储当前拖拽的元素信息
    let draggedItem = null;
    let dragType = null; // 'field' 或 'value'
    
    // 获取所有可拖拽的字段元素
    const fieldItems = document.querySelectorAll('.field-item[draggable="true"]');
    // 获取所有可拖拽的值元素
    const valueItems = document.querySelectorAll('.value-item[draggable="true"]');
    // 获取所有放置区域
    const dropZones = document.querySelectorAll('.drop-zone');
    
    // 初始化拖拽事件
    function initDragAndDrop() {
      // 设置字段拖拽事件
      fieldItems.forEach(item => {
        item.addEventListener('dragstart', handleFieldDragStart);
        item.addEventListener('dragend', handleDragEnd);
      });
      
      // 设置值拖拽事件
      valueItems.forEach(item => {
        item.addEventListener('dragstart', handleValueDragStart);
        item.addEventListener('dragend', handleDragEnd);
      });
      
      // 设置放置区域事件
      dropZones.forEach(zone => {
        zone.addEventListener('dragover', handleDragOver);
        zone.addEventListener('dragenter', handleDragEnter);
        zone.addEventListener('dragleave', handleDragLeave);
        zone.addEventListener('drop', handleDrop);
      });
    }
    
    // 处理字段拖拽开始
    function handleFieldDragStart(e) {
      draggedItem = this;
      dragType = 'field';
      this.classList.add('dragging');
      
      // 设置拖拽数据
      const fieldName = this.getAttribute('data-field');
      e.dataTransfer.setData('text/plain', fieldName);
      
      // 创建自定义拖拽图像
      const crt = this.cloneNode(true);
      crt.style.position = 'absolute';
      crt.style.top = '-1000px';
      document.body.appendChild(crt);
      e.dataTransfer.setDragImage(crt, 20, 20);
      
      // 短暂延迟后移除克隆元素
      setTimeout(() => {
        document.body.removeChild(crt);
      }, 0);
    }
    
    // 处理值拖拽开始
    function handleValueDragStart(e) {
      draggedItem = this;
      dragType = 'value';
      this.classList.add('dragging');
      
      // 设置拖拽数据
      const value = this.getAttribute('data-value');
      const field = this.getAttribute('data-field');
      e.dataTransfer.setData('text/plain', value);
      e.dataTransfer.setData('field', field);
      
      // 创建自定义拖拽图像
      const crt = this.cloneNode(true);
      crt.style.position = 'absolute';
      crt.style.top = '-1000px';
      document.body.appendChild(crt);
      e.dataTransfer.setDragImage(crt, 20, 20);
      
      // 短暂延迟后移除克隆元素
      setTimeout(() => {
        document.body.removeChild(crt);
      }, 0);
    }
    
    // 处理拖拽结束
    function handleDragEnd() {
      this.classList.remove('dragging');
      
      // 移除所有高亮
      document.querySelectorAll('.drop-zone').forEach(zone => {
        zone.classList.remove('bg-primary/10');
      });
    }
    
    // 处理拖拽经过
    function handleDragOver(e) {
      e.preventDefault(); // 允许放置
      this.classList.add('bg-primary/10');
    }
    
    // 处理拖拽进入
    function handleDragEnter(e) {
      e.preventDefault();
      this.classList.add('bg-primary/10');
    }
    
    // 处理拖拽离开
    function handleDragLeave() {
      this.classList.remove('bg-primary/10');
    }
    
    // 处理放置
    function handleDrop(e) {
      e.preventDefault();
      this.classList.remove('bg-primary/10');
      
      if (!draggedItem) return;
      
      // 获取拖拽数据
      const data = e.dataTransfer.getData('text/plain');
      const sourceField = e.dataTransfer.getData('field');
      
      // 获取目标元素信息
      const targetItem = this;
      const targetValue = targetItem.getAttribute('data-value');
      const targetField = targetItem.getAttribute('data-field');
      
      // 根据拖拽类型处理映射
      if (dragType === 'field') {
        // 处理字段映射
        const sourceField = draggedItem.getAttribute('data-field');
        
        if (sourceField && targetField) {
          // 添加到映射关系
          fieldMappings.set(sourceField, targetField);
          
          // 更新映射关系显示
          updateFieldMappingsDisplay();
          
          // 添加视觉反馈
          showConnection(draggedItem, targetItem, 'field');
          
          // 添加成功提示
          showToast(`成功映射字段: ${sourceField} → ${targetField}`);
          
          // 更新SQL
          updateGeneratedSQL();
        }
      } else if (dragType === 'value') {
        // 处理值映射
        if (sourceField && targetValue) {
          // 添加到映射关系
          valueMappings.set(data, targetValue);
          fieldValueMap.set(data, sourceField);
          
          // 更新映射关系显示
          updateValueMappingsDisplay();
          
          // 添加视觉反馈
          showConnection(draggedItem, targetItem, 'value');
          
          // 添加成功提示
          showToast(`成功映射值: ${data} → ${targetValue}`);
          
          // 更新SQL
          updateGeneratedSQL();
        }
      }
      
      // 重置拖拽状态
      draggedItem = null;
      dragType = null;
    }
    
    // 更新字段映射显示
    function updateFieldMappingsDisplay() {
      const container = document.getElementById('field-mappings');
      container.innerHTML = '';
      
      fieldMappings.forEach((target, source) => {
        const sourceItem = document.querySelector(`.field-item[data-field="${source}"]`);
        const targetItem = document.querySelector(`.field-item[data-field="${target}"]`);
        
        if (sourceItem && targetItem) {
          const sourceText = sourceItem.querySelector('span.font-medium').textContent;
          const targetText = targetItem.querySelector('span.font-medium').textContent;
          
          const mappingEl = document.createElement('div');
          mappingEl.className = 'bg-primary/5 p-3 rounded-lg flex justify-between items-center hover-scale';
          mappingEl.innerHTML = `
            <div class="flex items-center">
              <span class="bg-primary/10 text-primary px-2 py-1 rounded text-sm font-medium mr-2">${sourceText}</span>
              <i class="fa fa-long-arrow-right text-gray-400 mx-2"></i>
              <span class="bg-info/10 text-info px-2 py-1 rounded text-sm font-medium">${targetText}</span>
            </div>
            <button class="text-gray-400 hover:text-danger transition-all-300 delete-mapping" data-type="field" data-source="${source}">
              <i class="fa fa-times"></i>
            </button>
          `;
          
          container.appendChild(mappingEl);
        }
      });
      
      // 添加删除映射事件
      document.querySelectorAll('.delete-mapping[data-type="field"]').forEach(btn => {
        btn.addEventListener('click', function() {
          const source = this.getAttribute('data-source');
          fieldMappings.delete(source);
          
          // 同时删除相关的值映射
          fieldValueMap.forEach((field, value) => {
            if (field === source) {
              valueMappings.delete(value);
              fieldValueMap.delete(value);
            }
          });
          
          updateFieldMappingsDisplay();
          updateValueMappingsDisplay();
          showToast(`已删除字段映射: ${source}`);
          updateGeneratedSQL();
        });
      });
    }
    
    // 更新值映射显示
    function updateValueMappingsDisplay() {
      const container = document.getElementById('value-mappings');
      container.innerHTML = '';
      
      valueMappings.forEach((target, source) => {
        const field = fieldValueMap.get(source);
        const fieldItem = document.querySelector(`.field-item[data-field="${field}"]`);
        const fieldName = fieldItem ? fieldItem.querySelector('span.font-medium').textContent : field;
        
        const mappingEl = document.createElement('div');
        mappingEl.className = 'bg-info/5 p-3 rounded-lg flex justify-between items-center hover-scale';
        mappingEl.innerHTML = `
          <div class="flex items-center">
            <span class="bg-primary/10 text-primary px-2 py-1 rounded text-sm font-medium mr-2">${source}</span>
            <i class="fa fa-long-arrow-right text-gray-400 mx-2"></i>
            <span class="bg-info/10 text-info px-2 py-1 rounded text-sm font-medium">${target}</span>
            <span class="ml-2 text-xs text-gray-500">(${fieldName})</span>
          </div>
          <button class="text-gray-400 hover:text-danger transition-all-300 delete-mapping" data-type="value" data-source="${source}">
            <i class="fa fa-times"></i>
          </button>
        `;
        
        container.appendChild(mappingEl);
      });
      
      // 添加删除映射事件
      document.querySelectorAll('.delete-mapping[data-type="value"]').forEach(btn => {
        btn.addEventListener('click', function() {
          const source = this.getAttribute('data-source');
          valueMappings.delete(source);
          fieldValueMap.delete(source);
          updateValueMappingsDisplay();
          showToast(`已删除值映射: ${source}`);
          updateGeneratedSQL();
        });
      });
    }
    
    // 显示连接动画
    function showConnection(sourceEl, targetEl, type) {
      // 获取元素位置
      const sourceRect = sourceEl.getBoundingClientRect();
      const targetRect = targetEl.getBoundingClientRect();
      
      // 计算连接线路径
      const startX = sourceRect.right;
      const startY = sourceRect.top + sourceRect.height / 2;
      const endX = targetRect.left;
      const endY = targetRect.top + targetRect.height / 2;
      const controlX1 = startX + (endX - startX) / 3;
      const controlY1 = startY;
      const controlX2 = startX + 2 * (endX - startX) / 3;
      const controlY2 = endY;
      
      // 创建SVG路径
      const svgNS = "http://www.w3.org/2000/svg";
      const svg = document.createElementNS(svgNS, "svg");
      svg.setAttribute("width", window.innerWidth);
      svg.setAttribute("height", window.innerHeight);
      svg.setAttribute("class", "connection-line");
      
      const path = document.createElementNS(svgNS, "path");
      const pathData = `M ${startX} ${startY} C ${controlX1} ${controlY1}, ${controlX2} ${controlY2}, ${endX} ${endY}`;
      path.setAttribute("d", pathData);
      path.setAttribute("stroke", type === 'field' ? '#3b82f6' : '#06b6d4');
      path.setAttribute("stroke-width", "2");
      path.setAttribute("fill", "none");
      path.setAttribute("stroke-dasharray", "5,5");
      path.setAttribute("opacity", "0.7");
      
      svg.appendChild(path);
      document.body.appendChild(svg);
      
      // 动画效果
      let dashoffset = 10;
      const animatePath = () => {
        dashoffset--;
        if (dashoffset < 0) dashoffset = 10;
        path.setAttribute("stroke-dashoffset", dashoffset);
        
        if (dashoffset > 0) {
          requestAnimationFrame(animatePath);
        } else {
          // 动画结束后移除
          setTimeout(() => {
            svg.remove();
          }, 1000);
        }
      };
      
      animatePath();
    }
    
    // 显示提示消息
    function showToast(message) {
      const toast = document.createElement('div');
      toast.className = 'fixed bottom-4 right-4 bg-dark text-white px-4 py-2 rounded-lg shadow-lg transform translate-y-10 opacity-0 transition-all duration-300 z-50';
      toast.textContent = message;
      document.body.appendChild(toast);
      
      // 显示动画
      setTimeout(() => {
        toast.classList.remove('translate-y-10', 'opacity-0');
      }, 10);
      
      // 3秒后隐藏
      setTimeout(() => {
        toast.classList.add('translate-y-10', 'opacity-0');
        setTimeout(() => {
          document.body.removeChild(toast);
        }, 300);
      }, 3000);
    }
    
    // 更新生成的SQL
    function updateGeneratedSQL() {
      // 获取SELECT SQL元素
      const selectSqlEl = document.getElementById('select-sql');
      // 获取INSERT SQL元素
      const insertSqlEl = document.getElementById('insert-sql');
      // 获取UPDATE SQL元素
      const updateSqlEl = document.getElementById('update-sql');
      
      // 生成SELECT SQL
      let selectFields = [];
      
      fieldMappings.forEach((target, source) => {
        // 获取字段的中文名称
        const sourceItem = document.querySelector(`.field-item[data-field="${source}"]`);
        const sourceText = sourceItem ? sourceItem.querySelector('span.font-medium').textContent : source;
        
        // 检查是否有值映射
        let valueMappingCases = [];
        let hasValueMapping = false;
        
        valueMappings.forEach((targetValue, sourceValue) => {
          if (fieldValueMap.get(sourceValue) === source) {
            hasValueMapping = true;
            valueMappingCases.push(`        WHEN '${sourceValue}' THEN '${targetValue}'`);
          }
        });
        
        if (hasValueMapping) {
          // 有值映射，生成CASE语句
          let caseStmt = `    CASE a.${source}\n`;
          caseStmt += valueMappingCases.join('\n');
          caseStmt += `\n        ELSE a.${source}\n    END AS ${target}`;
          
          selectFields.push(caseStmt);
        } else {
          // 没有值映射，直接映射字段
          selectFields.push(`    a.${source} AS ${target}`);
        }
      });
      
      // 生成完整的SELECT SQL
      const selectSql = `SELECT
${selectFields.join(',\n')}
FROM
    表A a
LEFT JOIN
    表B b ON a.${getPrimaryKey()} = b.${fieldMappings.get(getPrimaryKey()) || 'user_id'};`;
      
      // 生成INSERT SQL
      const insertFields = Array.from(fieldMappings.values()).join(', ');
      const insertValues = Array.from(fieldMappings.keys()).map(key => {
        // 检查是否有值映射
        let valueMappingCases = [];
        let hasValueMapping = false;
        
        valueMappings.forEach((targetValue, sourceValue) => {
          if (fieldValueMap.get(sourceValue) === key) {
            hasValueMapping = true;
            valueMappingCases.push(`        WHEN '${sourceValue}' THEN '${targetValue}'`);
          }
        });
        
        if (hasValueMapping) {
          // 有值映射，生成CASE语句
          let caseStmt = `CASE a.${key}\n`;
          caseStmt += valueMappingCases.join('\n');
          caseStmt += `\n        ELSE a.${key}\n    END`;
          return caseStmt;
        } else {
          // 没有值映射，直接使用源字段
          return `a.${key}`;
        }
      }).join(',\n    ');
      
      const insertSql = `INSERT INTO 表B (${insertFields})
SELECT
    ${insertValues}
FROM
    表A a
WHERE
    NOT EXISTS (SELECT 1 FROM 表B b WHERE b.${fieldMappings.get(getPrimaryKey()) || 'user_id'} = a.${getPrimaryKey()});`;
      
      // 生成UPDATE SQL
      const updateSet = Array.from(fieldMappings.entries()).map(([source, target]) => {
        // 检查是否有值映射
        let valueMappingCases = [];
        let hasValueMapping = false;
        
        valueMappings.forEach((targetValue, sourceValue) => {
          if (fieldValueMap.get(sourceValue) === source) {
            hasValueMapping = true;
            valueMappingCases.push(`        WHEN '${sourceValue}' THEN '${targetValue}'`);
          }
        });
        
        if (hasValueMapping) {
          // 有值映射，生成CASE语句
          let caseStmt = `    b.${target} = CASE a.${source}\n`;
          caseStmt += valueMappingCases.join('\n');
          caseStmt += `\n        ELSE a.${source}\n    END`;
          return caseStmt;
        } else {
          // 没有值映射，直接赋值
          return `    b.${target} = a.${source}`;
        }
      }).join(',\n');
      
      const updateSql = `UPDATE 表B b
SET
${updateSet}
FROM
    表A a
WHERE
    b.${fieldMappings.get(getPrimaryKey()) || 'user_id'} = a.${getPrimaryKey()};`;
      
      // 更新UI
      selectSqlEl.textContent = selectSql;
      insertSqlEl.textContent = insertSql;
      updateSqlEl.textContent = updateSql;
      
      // 添加语法高亮类
      hljs.highlightElement(selectSqlEl);
      hljs.highlightElement(insertSqlEl);
      hljs.highlightElement(updateSqlEl);
    }
    
    // 获取主键字段名（简单假设ID为主键）
    function getPrimaryKey() {
      return 'id';
    }
    
    // 复制SQL到剪贴板
    function copySQLToClipboard(elementId, message) {
      const text = document.getElementById(elementId).textContent;
      navigator.clipboard.writeText(text).then(() => {
        showToast(message);
      }).catch(err => {
        showToast('复制失败，请手动复制');
        console.error('复制失败: ', err);
      });
    }
    
    // 页面加载完成后初始化
    document.addEventListener('DOMContentLoaded', () => {
      initDragAndDrop();
      updateFieldMappingsDisplay();
      updateValueMappingsDisplay();
      updateGeneratedSQL();
      
      // 添加复制按钮事件
      document.getElementById('copy-sql-btn').addEventListener('click', () => {
        copySQLToClipboard('select-sql', '已复制所有SQL');
      });
      
      document.getElementById('copy-select-sql').addEventListener('click', () => {
        copySQLToClipboard('select-sql', '已复制SELECT SQL');
      });
      
      document.getElementById('copy-insert-sql').addEventListener('click', () => {
        copySQLToClipboard('insert-sql', '已复制INSERT SQL');
      });
      
      document.getElementById('copy-update-sql').addEventListener('click', () => {
        copySQLToClipboard('update-sql', '已复制UPDATE SQL');
      });
    });
  </script>
  <!-- 引入代码高亮库 -->
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/highlight.js/11.7.0/styles/atom-one-dark.min.css">
  <script src="https://cdnjs.cloudflare.com/ajax/libs/highlight.js/11.7.0/highlight.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/highlight.js/11.7.0/languages/sql.min.js"></script>
</body>
</html>

效果如下：

字段与值映射：

字段映射+SQL：

字段+值映射+SQL：
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

jacky_wxl(微信同号）

喜欢作者

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值