mysql一起使用group by 和 order by 数据没有正常倒叙显示

SELECT * FROM is_face_customer WHERE dep_id = 7192 AND create_time > '2019-07-22 00:00:00' AND create_time < '2019-07-22 23:59:59' GROUP BY vip_id ORDER BY `create_time` DESC

想按照vip_id 分组，在按照create_time降序  但不会把最新一个人时间查出来。

 

解决办法1. 使用sql解决

select `vip_id` ,`create_time`  from (select * from is_face_customer WHERE dep_id = 7192 and `is_valid` = 1 and create_time > '2019-07-23 00:00:00'
AND create_time <'2019-07-23 23:59:59'  order by `create_time`  desc LIMIT  10000) T group by vip_id order by `create_time` desc

mysql版本是5.6的要加上limit 否则不会成功。。。

2.使用代码解决

@Test
	public void test(){
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		
		List<FaceCustomer> result = new ArrayList<FaceCustomer>();
		//先group by 把所有人查出来
		List<FaceCustomer> itList = faceCustomerMapper.selectFaceCustomerBydepId(null);

		for (FaceCustomer faceCustomer : itList) {      
        //在去按order by查询最新的一条数据      
			FaceCustomer faceCustomers = faceCustomerMapper.selectLastFaceCustomerByVipId(faceCustomer.getVipId());
    //加到一个新的list中 每个人都是最新的一条数据
			result.add(faceCustomers);
		}
		//按创建时间降序
		Collections.sort(result, new MyComparator());
		
		for (FaceCustomer faceCustomer : result) {
            //输出看下是正确的
			System.out.println(sdf.format(faceCustomer.getCreateTime()));
		}
		
		System.out.println();
	}
	
	//按时间降序方法
	public class MyComparator implements Comparator<FaceCustomer> { 
		  @Override 
		  public int compare(FaceCustomer p1,FaceCustomer p2) { 
		    return p2.getCreateTime().compareTo(p1.getCreateTime()); 
		  } 
		 
	}

第一种简单 但是要考虑数据库压力，第二种比较复杂，数据库压力小。