【sql】根据两个字段的值组合情况去自定义第三个字段

【博文已迁移，持续更新请关注：https://zorkelvll.cn/blogs/zorkelvll/articles/2018/11/02/1541172492556】

 

场景2：

    根据两个字段的值组合情况去自定义第三个字段

原始表：

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	MARKET	MARKET_LISTBOARDNAME
600129.SH	主板	SH	沪市A股
000606.SZ	主板	SZ	深A主板
002152.SZ	中小企业板	SZ	中小板
300458.SZ	创业板	SZ	创业板

    解决思路：

-- 创建列名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;