mysql取分组后最新的一条记录

mysql取分组后最新的一条记录,下面两种方法.一种是先筛选 出最大和最新的时间,在连表查询.一种是先排序,然后在次分组查询(默认第一条),就是最新的一条数据了

#select * from t_assistant_article as a, (select max(base_id) as base_id, max(create_time) as create_time from t_assistant_article as b group by base_id ) as b where a.base_id=b.base_id and a.create_time = b.create_time
#select base_id,max(create_time), max(article_id) as article_id from t_assistant_article as b group by base_id 
select * from (select * from t_assistant_article order by create_time desc) as a group by base_id

分组查询最新记录的两种方式
##第一种
select
			*
		from
			IVMP_DEVICE d,
			IVMP_DEVICE_STATE ds
		where
			d.DEVICE_SERIES = ds.DEVICE_SERIES
		and ds.HAPPEN_TIME = (
			select
				max(HAPPEN_TIME)
			from
				IVMP_DEVICE_STATE ids
			where
				ids.DEVICE_SERIES = ds.DEVICE_SERIES)
	     group by ds.DEVICE_SERIES	

##第二种
找出分组的最新时间，再作为子查询查出分组最新记录，
SELECT
	*
FROM
	IVMP_DEVICE_STATE AS ds,
	(
		SELECT
			DEVICE_SERIES,
			max(HAPPEN_TIME) as HAPPEN_TIME
		FROM
			IVMP_DEVICE_STATE ids
		GROUP BY
			DEVICE_SERIES
	) AS b
WHERE
	ds.DEVICE_SERIES = b.DEVICE_SERIES
AND ds.HAPPEN_TIME = b.HAPPEN_TIME
ORDER BY ds.DEVICE_SERIES

下图是两种sql查询方式对比，第二种要比第一种查询效率高很多

批量更新字段方法

UPDATE GOODS g 
SET  g.`ORG_ID`=(
SELECT tmp.ORG_ID 
from (
SELECT o.OPERATOR_ID,org.ORG_ID from OPERATOR o 
LEFT JOIN ORG org on org.OPERATOR_ID = o.OPERATOR_ID 
where  org.ORG_CODE='JG000001'
) tmp 
where g.OPERATOR_ID = tmp.OPERATOR_ID


) ,`CASCADE_ID`='0'