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'