文章目录

zorke的博客

@一只不甘沦为码农的程序猿

X

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

  zorkelvll

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;

标题:MySQL根据某两列值做笛卡尔积运算构造第三列
作者:zorkelvll
地址:http://blog.zorkelvll.cn/solo/articles/2019/05/19/1558237646722.html