分类 标签 存档 社区 博客 友链 GitHub 订阅 搜索

MySQL 根据某两列值做笛卡尔积运算构造第三列

188 浏览

ZERO

    持续更新 请关注:https://zorkelvll.cn/blogs/zorkelvll/articles/2018/11/02/1541172492556

背景

   本文主要是针对在实际应用场景中出现的 “需要根据某两列即两个字段的值排列组合(也即笛卡尔积)构造出第三列” 的这样一种需求,提供一种解决方案实践,并结合具体的实例给出 sql 代码!

场景:

  • 原始表:
S_INFO_WINDCODE S_INFO_LISTBOARDNAME
600129.SH 主板
000606.SZ 主板
002152.SZ 中小企业板
300458.SZ 创业板
  • 构造逻辑:

   根据字段 S_INFO_WINDCODE 中的值的 SH 和 SZ,与 S_INFO_LISTBOARDNAME 中的值组合构成一个第三个字段 MARKET_LISTBOARDNAME,也即如下表

  • 目标表:
S_INFO_WINDCODE S_INFO_LISTBOARDNAME S_INFO_LISTBOARDNAME S_INFO_LISTBOARDNAME
600129.SH 主板 SH 沪市 A 股
000606.SZ 主板 SZ 深 A 主板
002152.SZ 中小企业板 SZ 中小板
300458.SZ 创业板 SZ 创业板
  • 解决思路:

SQL

--创建列名MARKET--
SET @dbname = DATABASE();  
SET @tablename = "asharedescription";
SET @columnname = "MARKET";
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (table_name = @tablename)
      AND (table_schema = @dbname)
      AND (column_name = @columnname)
  ) > 0,
  "SELECT 1",
  CONCAT("ALTER TABLE ", @tablename, " ADD ", @columnname, " VARCHAR(10) NOT NULL default 'NO';")
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

--创建列名MARKET_LISTBOARDNAME--
SET @dbname = DATABASE();
SET @tablename = "asharedescription";
SET @columnname = "MARKET_LISTBOARDNAME";
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE
      (table_name = @tablename)
      AND (table_schema = @dbname)
      AND (column_name = @columnname)
  ) > 0,
  "SELECT 1",
    CONCAT("ALTER TABLE ", @tablename, " ADD ", @columnname, " VARCHAR(10) NOT NULL default 'NO';")
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 生成组合的数据 --
SELECT a.s_info_windcode,
substring_index(a.S_INFO_WINDCODE, '.', -1) MARKET,
a.S_INFO_LISTBOARDNAME,
CASE 
  WHEN substring_index(a.S_INFO_WINDCODE, '.', -1)='SH' AND 
S_INFO_LISTBOARDNAME='主板' THEN '沪市A股'
  WHEN substring_index(a.S_INFO_WINDCODE, '.', -1)='SZ' AND 
S_INFO_LISTBOARDNAME='主板' THEN '深A主板'
  WHEN S_INFO_LISTBOARDNAME='中小企业板' THEN '中小板'
  WHEN S_INFO_LISTBOARDNAME='创业板' THEN '创业板'
  ELSE NULL END MARKET_LISTBOARDNAME
FROM asharedescription a;

-- 更新数据 --
UPDATE asharedescription ad,
(
-- start 生成组合的数据  
SELECT a.s_info_windcode,
substring_index(a.S_INFO_WINDCODE, '.', -1) MARKET,
a.S_INFO_LISTBOARDNAME,
CASE 
  WHEN substring_index(a.S_INFO_WINDCODE, '.', -1)='SH' AND 
S_INFO_LISTBOARDNAME='主板' THEN '沪市A股'
  WHEN substring_index(a.S_INFO_WINDCODE, '.', -1)='SZ' AND 
S_INFO_LISTBOARDNAME='主板' THEN '深A主板'
  WHEN S_INFO_LISTBOARDNAME='中小企业板' THEN '中小板'
  WHEN S_INFO_LISTBOARDNAME='创业板' THEN '创业板'
  ELSE NULL END MARKET_LISTBOARDNAME
FROM asharedescription a
-- end 生成组合的数据  
) AS tt
SET ad.MARKET = tt.MARKET,ad.MARKET_LISTBOARDNAME = 
tt.MARKET_LISTBOARDNAME
WHERE tt.S_INFO_WINDCODE = ad.S_INFO_WINDCODE;

评论  
留下你的脚步
推荐阅读