继续昨天的问题:
2.sql语句查询的时候,应该使用分组查询,一共两种类型的数据,分别取每组数据的最新数据(完成)
按照正常来写,写成这样就可以了,但是本系统使用的动态sql,需要拼接,不能按照下面这样写。
SELECT
*
FROM
( SELECT * FROM `stockindex_world` ORDER BY UpdateTime desc limit 10000 ) table1
GROUP BY Type;
于是,改写成这样,这样是有bug的,感觉分组就没起啥作用 ,(得把Type,ID放在一起,不然只能返回各种类型的一条)
<select id="findManyByHk" resultMap="BaseResultMap" parameterType="com.simnectzbank.lbs.systemlayer.stockinfo.entity.IndexHistoryEntity" >
select * from stockindex_hk
where 1=1
<if test="type != null and type != ''" >
and Type = #{type}
</if>
<if test="selectDate != null and selectDate !=''">
and UpdateTime >= #{fromDate}
and UpdateTime < #{toDate}
</if>
<if test="selectDate == null or selectDate ==''">
group by Type,ID
order by UpdateTime desc limit 1
</if>
</select>
<select id="findManyByChina" resultMap="BaseResultMap" parameterType="com.simnectzbank.lbs.systemlayer.stockinfo.entity.IndexHistoryEntity" >
select * from stockindex_china
where 1=1
<if test="type != null and type != ''" >
and Type = #{type}
</if>
<if test="selectDate != null and selectDate !=''">
and UpdateTime >= #{fromDate}
and UpdateTime < #{toDate}
</if>
<if test="selectDate == null or selectDate ==''">
group by Type,ID
order by UpdateTime desc limit 2
</if>
</select>
<select id="findManyByWorld" resultMap="BaseResultMap" parameterType="com.simnectzbank.lbs.systemlayer.stockinfo.entity.IndexHistoryEntity" >
select * from stockindex_world
where 1=1
<if test="type != null and type != ''" >
and Type = #{type}
</if>
<if test="selectDate != null and selectDate !=''">
and UpdateTime >= #{fromDate}
and UpdateTime < #{toDate}
</if>
<if test="selectDate == null or selectDate ==''">
group by Type,ID
order by UpdateTime desc limit 2
</if>
</select>
另外还必须先修改 select @@global.sql_mode,涉及到group by
将其修改为
set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
3.输入的是时间戳,将时间戳转化为两个时间段,比如输入的是1618826381,日期是2021-04-19 17:59:41 那么对应输入数据库查询的日期为 2021-04-19------2021-04-20之间(完成)
这段很完美!!
package com.simnectzbank.lbs.processlayer.stock.util;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
public class DateToStamp {
public static String date2Stamp(String s) throws ParseException {
String res;
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
if(s != null && !s.equals("")){
Date date =simpleDateFormat.parse(s);
long ts = date.getTime();
res = String.valueOf(ts);
return res;
}
return s;
}
public static Map<String,String> stamp2Date(String stamp) throws ParseException {
String sd0 ="";
String sd12 ="";
Date d = new Date();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
if(stamp != null && !stamp.equals("")){
sd0 = sdf.format(new Date(Long.parseLong(stamp)));
Date sd00 = sdf.parse(sd0);//转成data类型的
Calendar c = Calendar.getInstance();
c.setTime(sd00);
c.add(Calendar.DATE, 1);
sd12 = sdf.format(c.getTime());
sd0 = date2Stamp(sd0);//转成时间戳
sd12 = date2Stamp(sd12);//转成时间戳
Map<String,String> map = new HashMap<String, String>();
map.put("sd0",sd0);
map.put("sd12",sd12);
return map;
}else{
Map<String,String> map = new HashMap<String, String>();
map.put("sd0",stamp);
map.put("sd12",stamp);
return map;
}
}
public static void main(String[] args) throws ParseException {
// String t = "";
// String s = date2Stamp(t);
// System.out.println(s);
// Map<String, String> map = stamp2Date("1618217382172");
Map<String, String> map = stamp2Date(null);
System.out.println( map.get("sd0"));
System.out.println( map.get("sd12"));
}
}
4.计算那些升跌率呀什么的。
很麻烦这里。
1.首先先将数据库里没有的三个字段补上,关键部分是在stock-process这层的(在这里卡了接近两小时呢)
StockIndexHistoryModel、StockIndexModel加入以下代码,当然experience层中也要添加
/*额外需要展示的字段*/
@ApiModelProperty(notes = "升跌.<br>maxLength: 20</br>")
private BigDecimal upDown;
@ApiModelProperty(notes = "升跌(%).<br>maxLength: 20</br>")
private BigDecimal upDownPercentage;
@ApiModelProperty(notes = "本周升跌(%).<br>maxLength: 20</br>")
private BigDecimal upDownWeek;
结果如下:(现在还没有给其赋值呢)