image
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;