前言:MySql没有像SqlServer 和 Oracle那样的Row_Number(Partition by)分组函数,但是可以用SUBSTRING_INDEX和GROUP_CONCAT2个函数实现这样的效果
表1 t_user:
表2 t_student:
需求:根据表2【create_time】倒序,根据表1【t_sex】分组,取表1每种【t_sex】对应的第一条表2信息
sql代码:
1.首先关联2张表:
SELECT a.t_sex,a.t_name,a.t_score,b.t_sName,b.t_sPhone,b.create_time from t_user as a
INNER JOIN t_student as b on a.t_id=b.t_id
结果:
2.用SUBSTRING_INDEX和GROUP_CONCAT2个函数实现效果:
SELECT a.t_sex,
SUBSTRING_INDEX(GROUP_CONCAT(a.t_name ORDER BY b.create_time DESC),',',1) as t_name,
SUBSTRING_INDEX(GROUP_CONCAT(a.t_score ORDER BY b.create_time DESC),',',1) as t_score,
SUBSTRING_INDEX(GROUP_CONCAT(b.t_sName ORDER BY b.create_time DESC),',',1) as t_sName,
SUBSTRING_INDEX(GROUP_CONCAT(b.t_sPhone ORDER BY b.create_time DESC),',',1) as t_sPhone,
SUBSTRING_INDEX(GROUP_CONCAT(b.create_time ORDER BY b.create_time DESC),',',1) as create_time FROM
t_user as a
INNER JOIN t_student as b on a.t_id=b.t_id
GROUP BY a.t_sex;
结果:
注意:这里GROUP_CONCA里面一定要指定排序的字段,不然顺序会乱,取值的时候也不能对应!!!