image
ZERO
????持續(xù)更新 請關(guān)注：https://zorkelvll.cn/blogs/zorkelvll/articles/2018/11/02/1541172492556
背景
??本文主要是針對(duì)在實(shí)際應(yīng)用場景中出現(xiàn)的“需要根據(jù)某兩列即兩個(gè)字段的值排列組合（也即笛卡爾積）構(gòu)造出第三列”的這樣一種需求咏窿，提供一種解決方案實(shí)踐，并結(jié)合具體的實(shí)例給出sql代碼！
場景：
- 原始表：
| S_INFO_WINDCODE | S_INFO_LISTBOARDNAME |
|---|---|
| 600129.SH | 主板 |
| 000606.SZ | 主板 |
| 002152.SZ | 中小企業(yè)板 |
| 300458.SZ | 創(chuàng)業(yè)板 |
- 構(gòu)造邏輯：
??根據(jù)字段S_INFO_WINDCODE中的值的SH和SZ，與S_INFO_LISTBOARDNAME中的值組合構(gòu)成一個(gè)第三個(gè)字段MARKET_LISTBOARDNAME，也即如下表
- 目標(biāo)表：
| S_INFO_WINDCODE | S_INFO_LISTBOARDNAME | S_INFO_LISTBOARDNAME | S_INFO_LISTBOARDNAME |
|---|---|---|---|
| 600129.SH | 主板 | SH | 滬市A股 |
| 000606.SZ | 主板 | SZ | 深A(yù)主板 |
| 002152.SZ | 中小企業(yè)板 | SZ | 中小板 |
| 300458.SZ | 創(chuàng)業(yè)板 | SZ | 創(chuàng)業(yè)板 |
- 解決思路：
SQL
--創(chuàng)建列名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;
--創(chuàng)建列名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;
-- 生成組合的數(shù)據(jù) --
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(yù)主板'
WHEN S_INFO_LISTBOARDNAME='中小企業(yè)板' THEN '中小板'
WHEN S_INFO_LISTBOARDNAME='創(chuàng)業(yè)板' THEN '創(chuàng)業(yè)板'
ELSE NULL END MARKET_LISTBOARDNAME
FROM asharedescription a;
-- 更新數(shù)據(jù) --
UPDATE asharedescription ad,
(
-- start 生成組合的數(shù)據(jù)
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(yù)主板'
WHEN S_INFO_LISTBOARDNAME='中小企業(yè)板' THEN '中小板'
WHEN S_INFO_LISTBOARDNAME='創(chuàng)業(yè)板' THEN '創(chuàng)業(yè)板'
ELSE NULL END MARKET_LISTBOARDNAME
FROM asharedescription a
-- end 生成組合的數(shù)據(jù)
) AS tt
SET ad.MARKET = tt.MARKET,ad.MARKET_LISTBOARDNAME =
tt.MARKET_LISTBOARDNAME
WHERE tt.S_INFO_WINDCODE = ad.S_INFO_WINDCODE;