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

ZERO

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

背景

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

场景：

• 原始表：

• 构造逻辑：

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

• 目标表：

• 解决思路：

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;