11月点滴积累

JAVA语法相关

1.替换中文逗号:使用str = str.replace("，"  ,  ","); 可成功解决

2.获取6位随机数:

Random random = new Random();
Integer r7 = random.nextInt(899999);
r7 = r7 + 100000;
String s7 = r7.toString();

3.ResourceUtils.getFile("classpath:config.json")     此方法无法从jar包中获取文件

sql语法相关

1.拼串:group_concat语法

SELECT
   AttributeName as name,
   GROUP_CONCAT(AttributeData) as `value`
FROM ***
WHERE *** = #{v}
GROUP BY AttributeName

以上sql的结果每行样式为: name,(value1,value2,value3)

2.查询的结果去重,只是查询结果去掉重复,表里面的重复数据并没有更改:

 

left join
(SELECT c.`brand_ID`,c.category_id ,COUNT(DISTINCT c.`brand_ID`) FROM category_brand c GROUP BY c.`brand_ID`) cb
on b.ID = cb.brand_ID

以上sql语句会将绿色的那列去重

 

3.查询时将日期格式化:DATE_FORMAT(oi.ORDER_DATE,'%Y-%m-%d')     样式2018-12-13

4.求和并过滤空值,遇到null则将其设为0:SUM(IFNULL(`需求和的列`,0))