实际开发过程中,总会遇到一些特定的问题,所以也需要一些特殊的解决方法,记录一次最近的SQL例子,A,B表连表查询,关系为一对多,但是查询结果只能为一条,也就是一条A的数据,对应B表很多条数据,但是只取B表最新的一条数据,然后再B表最新的数据中读取一个Json类型字段中的多个值,其中一个值又是一个json类型,还要更深层次获取json中的json中的属性值,再用as建立别名查询出来,你能想到几种方式呢?
我的第一反应不是用Sql语句解决,毕竟我觉得Sql太复杂了影响性能,我的思路:
一,数据肯定连表查,子查询查出B表最新的A表对应的数据,然后再和A表连表查,后台直接用JSONOBJECT来接收,注意使用自定义的TypeHandler,不然会类型解析错误,JSONOBJECT类型其实和Map很像,直接通过Get方法就可以,直接先获取到外层Json,再获取里层的Json,
1 | JSONObject report = (JSONObject) JSONObject.parse(record.getReport()); |
是不是很简单,思路也很清晰,但是组长告诉我不能这样处理,因为外层的Json里面字段很多,而我这里查询的其实只是里层json的几个字段而已,这样查出来再做处理,并没有比直接Sql语句查询简单,要我用Sql搞定,没办法,领导最大,尝试第二种方法,
二,这就要使用到JSON_EXTRACT,配合Replace函数去掉值的引号,来看Sql,
注:JSON_EXTRACT直接获取Json类型字段,第一个参数是对应的Json类型的字段,第二个字段是获取里面的值,用$来获取值,该函数可以直接获取里外层的Json中的值;
REPLACE函数可以替换字符串多余的引号,否则后台查出来的值的内容是””\stat””,当然也可以后台遍历使用字符串的replace方法,但是都决定Sql搞定了,又何必还要后台再遍历一次呢。
1 | # 第一步,先分组查询B表最新的数据, |
查询结果:
我觉得写的还算详尽了,也是一次很好的学习体验,JSON_EXTRACT也是第一次使用,发现意外的得劲,当然Sql别忘记添加索引,我这里用的联合索引,box_id和create_time,注意时间放在后面,不然会导致索引失效(范围索引后的索引失效),不懂的可以参看一下我的Mysql进阶-索引失效准则,如果有问题还请指教,谢谢。