最近在开发过程中,遇到一个问题,查询多个用户的最后一次下单时间。
表结构就是一张用户表USER和一张订单表ORDER,USER和ORDER是一对多的关系。
这个查询的大体思路就是用ORDER关联USER表,先按照下单时间进行倒序排序,取第一个,然后对客户ID进行分组,最后查询出要的结果。
经过查找一些资料,发现了GROUP_CONCAT
它的完整语法为:group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])
上代码:
SELECT
mo.customer_id,
cu.user_name,
cu.mobile,
cu.sex,
cu.age,
cu.regist_time,
SUBSTRING_INDEX(GROUP_CONCAT( mo.create_time ORDER BY mo.create_time DESC ), ',', 1 )
FROM order mo
LEFT JOIN user cu ON mo.customer_id = cu.user_id
GROUP BY mo.customer_id
这其中还用到了一个关键字SUBSTRING_INDEX
它的完整语法为:SUBSTRING_INDEX(str,delim,count) 。
在不加SUBSTRING_INDEX的情况下,输出结果为:
在加了SUBSTRING_INDEX之后,取最后一笔时间,结果为:
综上发现,GROUP_CONCAT这个关键字在一对多需要分组查询的情况下可谓是神器,可以用很简短的语句查询出自己想要的结果。用GROUP_CONCAT获取结果的时候,里面的数据会用逗号分隔开,在接口中用split进行截取,然后对这个结果进行遍历,就可以获得每一条的数据了;但是还有一点要注意的是:当关联到的数据有个别是NULL的情况下会出现查询缺少字段的情况,NULL不会以任何形式展现到结果中,比如:aaa,NULL,bbb;实际结果是 aaa,bbb这样的话很容易出现数据混乱,所以在查询过程中要加入判断:GROUP_CONCAT( IFNULL(mr.medical_id,'') ) AS relatedMedicalId。其中用到的关键字是IFNULL。
它的完整语法为IFNULL(expr1,expr2);
假如expr1 不为NULL,则 IFNULL() 的返回值为 expr1;否则其返回值为 expr2。
IFNULL()的返回值是数字或是字符串,具体情况取决于其所使用的语境。
它表示如果字段为NULL的话,会将字段转换成‘ ’ 空字符串,GROUP_CONCAT不会将空字符串进行过滤,得到的结果为aaa,,bbb。