-- 以周k线展示功能为例分析-- 业务概述:周k线就是统计过去每周产生的数据,形成的K线图-- 包含的数据:周1的开盘价、周五的收盘价、一周内的最价、一周内的最低价、股票的code等;-- SQL分析:-- 1.先根据周进行分组,分组后获取周1开盘的时间点、周五收盘的时间点、max最高价、min最低价等;-- 2.将步骤1的开盘和收盘时间点结合股票的code查询出对应的价格,就是一周内的开盘价和收盘价;-- 具体SQL:-- 步骤1SQL:SELECT
stock_code,
DATE_FORMAT(cur_time,'%Y-%u')AS week,MAX(cur_time)AS mxTime,//收盘时间点,对应的价格,就是收盘价格MIN(cur_time)AS miTime,//开盘时间点,对应的价格,就是开盘价MAX(cur_price)AS maxPrice,MIN(cur_price)AS minPrice,AVG(cur_price)AS avgPrice
FROM stock_rt_info
WHERE stock_code=#{stockCode} and cur_time between #{startTime} and #{endTime}GROUPBY week
# 步骤2:将步骤1的结果作为一张表与股票流水表关联查询即可 SELECT tmp.stock_code,tmp.maxPrice,tmp.minPrice,tmp.avgPrice,s1.cur_price AS openPrice,s2.cur_price AS closePrice,
DATE_FORMAT(tmp.mxTime,'%Y%m%d')AS mxTime FROM(SELECT
stock_code,
DATE_FORMAT(cur_time,'%Y-%u')AS week,MAX(cur_time)AS mxTime,MIN(cur_time)AS miTime,MAX(cur_price)AS maxPrice,MIN(cur_price)AS minPrice,AVG(cur_price)AS avgPrice
FROM stock_rt_info
WHERE stock_code=#{stockCode} and cur_time between #{startTime} and #{endTime}GROUPBY week
)AS tmp LEFTJOIN stock_rt_info AS s1 ON s1.stock_code=tmp.stock_code AND s1.cur_time=tmp.miTime
LEFTJOIN stock_rt_info AS s2 ON s2.stock_code=tmp.stock_code AND s2.cur_time=tmp.mxTime ORDERBY mxTime ASC# 正常情况下,股票的数据肯定是有的,没必有左外连接查询的,但是如果防止关联查询时,表中无数据,则可使用外连接查询;