mysql 同表通过表字段更改另一个字段的值

update t_device u set u.F_PoleNum=( select c.F_PoleNum from  
(select  CONCAT('440306',
(SELECT substring(trim(F_Code),1,2)  FROM t_device a where a.F_Code=d.F_Code),
(SELECT substring(trim(F_Code),-4)  FROM t_device b where b.F_Code=d.F_Code) 
) as F_PoleNum ,d.F_Code as F_Code  from t_device d ) c  where substring(c.F_PoleNum ,-4)=substring(u.F_Code,-4) and substring(c.F_PoleNum,7,2)=substring(u.F_Code,1,2))

以上是sql实现

2,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

 3,实现查找最近一周、一个月、三个月的数据

当前时间：

mysql> select now();

下一个月：
mysql> select date_add(now(),interval 1 month);

上一个月：
mysql> select date_add(now(),interval -1 month);