当前位置：首页 > article >正文

MySQL 按照条件（分组）只取一个形成列表 group max

article 2024/9/19 22:07:54

方法一、通过Max形成where条件

SELECT
	t1.* 
FROM
	biz_customer_hold AS t1 
WHERE
	t1.ch_create_time = ( 
        SELECT MAX( ch_create_time ) 
        FROM biz_customer_hold AS t2 WHERE t2.ch_cust_no = t1.ch_cust_no ) 
ORDER BY
	t1.ch_create_time DESC,
	t1.ch_hold_time DESC

方法二、通过NOT EXISTS，通过复制表判断不存在比自己大的，其实也是max的意思。

SELECT
	t1.* 
FROM
	biz_customer_hold AS t1 
WHERE
	NOT EXISTS ( SELECT 1 FROM biz_customer_hold 
                WHERE ch_cust_no = t1.ch_cust_no AND 
                ch_hold_status = t1.ch_hold_status AND 
                ch_create_time > t1.ch_create_time ) 
ORDER BY
	t1.ch_create_time DESC,
	t1.ch_hold_time DESC

方法三、通过和符合条件的小表关系，与方法一参不多

SELECT t1.* FROM biz_customer_hold AS t1,

( SELECT ch_cust_no, MAX( ch_create_time ) AS ch_create_time 
FROM biz_customer_hold GROUP BY ch_cust_no 
) AS t2 

WHERE
	t2.ch_create_time = t1.ch_create_time

方法四、与方法二的No Exist 原理相同，只是通过where 条件判断

SELECT
	t1.* 
FROM
	biz_customer_hold AS t1 
WHERE
	( SELECT COUNT(*) 
      FROM biz_customer_hold 
      WHERE ch_cust_no = t1.ch_cust_no AND 
      ch_create_time > t1.ch_create_time ) =0