MySQL视图小例子

场景：

　　某查询接口 查询sql语句已确定，用该sql语句去查 表 t_strategy_stock 中的数据，但是 表t_strategy_stock 的字段名称和 sql 语句中写死的名称不同。

需求：

　　查询接口不做改动，sql语句不变，从表 t_strategy_stock 中查出数据。

解决方法：

　　新建视图，把 表t_strategy_stock 中的字段名称映射成 和查询接口 SQL 语句中相同的名称，然后从视图中查询数据。

 

 

1.带字段判断和字符串连接 concat 的例子：

select `t`.`formula_id` AS `gscode`,concat(convert(if((`t`.`market` = 0),'SZ','SH') using utf8),`t`.`code`) AS `gpcode`,`t`.`date` AS `ymd`,`t`.`time` AS `hms`,`t`.`price` AS `price`,`t`.`zdf` AS `zdf` 
from `t_strategy_stock` `t`

表 t_strategy_stock ：

视图 v_strategy_stock ：

 

 

2.带字符串截取substr的例子：

select `t`.`formula_id` AS `formula_id`, `t`.`period_type` AS `period_type`, `t`.`ymd` AS `ymd`, if( (convert(substring(`t`.`gpcode`,1,2)using utf8) = 'SZ'), 0,1) AS `gpMarket`, convert(substring(`t`.`gpcode`,3,6)using utf8) AS `gpcode`, `t`.`dayNum` AS `dayNum`
from `signaldata` `t`　

 表 signaldata：

视图 v_signaldata：

 

select case convert(`t`.`gscode` using utf8)
	when 'DYYH' then 37
	when 'DYEH' then 38
	when 'HJY' then 39
	when 'PKS' then 40 end
AS `formula_id`, 7 AS `period_type`, `t`.`ymd` AS `ymd`, if( (convert(substring(`t`.`gpcode`,1,2)using utf8) = 'SZ'), 0,1) AS `gpMarket`, convert(substring(`t`.`gpcode`,3,6)using utf8) AS `gpcode`, `t`.`f1` AS `f1`, `t`.`f2` AS `f2`, `t`.`f3` AS `f3`, `t`.`f4` AS `f4`, `t`.`f5` AS `f5`, `t`.`f6` AS `f6`, `t`.`f7` AS `f7`, `t`.`f8` AS `f8`, `t`.`f9` AS `f9`, `t`.`f10` AS `f10`, `t`.`f11` AS `f11`, `t`.`f12` AS `f12`, `t`.`f13` AS `f13`, `t`.`f14` AS `f14`, `t`.`f15` AS `f15`, `t`.`f16` AS `f16`, `t`.`rise_max` AS `rise_max`, `t`.`rise_signalDay` AS `rise_signalDay`, `t`.`high_max` AS `high_max`, `t`.`yClose_signalDay` AS `yClose_signalDay`
from `calcgsdataflash` `t` 

表：calcgsdataflash

 

视图：v_calcgsdataflash

 

select case formula_id
    when '4' then 'DXJJ' end
AS `gscode`,concat(convert(if((`t`.`gpMarket` = 0),'SZ','SH') using utf8),`t`.`gpcode`) AS `gpcode`,`t`.`ymd` AS `ymd`,
( floor(`t`.`hms` / 10000) * 60 + floor(`t`.`hms` % 10000 / 100) ) AS `hms`,
`t`.`f1` AS `f1`, `t`.`f2` AS `f2`, `t`.`f3` AS `f3`, `t`.`f4` AS `f4`, `t`.`f5` AS `f5`, `t`.`f6` AS `f6`, ( floor(`t`.`f7` / 10000) * 60 + floor(`t`.`f7` % 10000 / 100) ) AS `f7`, `t`.`f8` AS `f8`, `t`.`f9` AS `f9`, `t`.`f10` AS `f10`, `t`.`f11` AS `f11`, `t`.`f12` AS `f12`, `t`.`f13` AS `f13`, `t`.`f14` AS `f14`, `t`.`f15` AS `f15`, `t`.`f16` AS `f16`, `t`.`rise_max` AS `rise_max`, `t`.`rise_signalDay` AS `rise_signalDay`, `t`.`high_max` AS `high_max`, `t`.`yClose_signalDay` AS `yClose_signalDay`
from `stockpooldata_flash` `t` 

　　

 

3.联表查询新建视图

场景：需要从表 t_fid_254获取 market、code、date、buy、sell，从表 stockqt 获取 name字段

select `t`.`market` AS `market`,`t`.`code` AS `code`,`t`.`date` AS `date`,substr(`t`.`data`,2,1) AS `buy`,substr(`t`.`data`,(length(`t`.`data`) - 1),1) AS `sell`,
`qt`.`gpName` AS `name` 
from (`t_fid_254` `t` left join `stockqt` `qt` on(((`t`.`code` = `qt`.`gpcode`) and (`t`.`market` = `qt`.`gpMarket`))))

表 t_fid_254：

表 stockqt：

新建的视图 v_t_fid_254：

 

4.创建按指定字符分割字符串函数

场景：需要将data字符串( 格式：[1,-1895154,,1] )按逗号分割

解决方法：

　　1.创建一个自定义函数 SPLIT_STR 完成分割功能　

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

　　2.使用SPLIT_STR函数按逗号分割data

SELECT `t`.`market` as `market`, 
`t`.`code` as `code`,
`t`.`date` as `date`,
`t`.`time` as `time`,
SPLIT_STR(`t`.`data`, ',', 2) as `ZJQD`,
SPLIT_STR(`t`.`data`, ',', 3) as `ZLZB`,
SPLIT_STR(`t`.`data`, ',', 4) as `SHZB`,
SPLIT_STR(SPLIT_STR(`t`.`data`, ',', 6), ']', 1) as `ZJQDZB`,
`qt`.`gpName` as `name`
from (`t_fid_8` as `t` left join `stockqt` as `qt` on((`t`.`code` = `qt`.`gpcode`) and (`t`.`market` = `qt`.`gpMarket`)) )