字段
d_one d_time_one d_two d_timetwo
1 2009-2-1 0
2 2009-2-2 1
3 2009-2-2 1
4 2009-2-5 1
5 null 2,3,4想要更新字段d_time_two,数据从d_time_one中更新来,更新的规则是:d_one的编号在d_two中,对应当d_time_one的最大值
其中d_two表示当前记录的父类编号
---想要的结果
d_one d_time_one d_two d_timetwo
1 2009-2-1 0
2 2009-2-2 1 2009-2-1
3 2009-2-2 1 2009-2-1
4 2009-2-5 1 2009-2-1
5 null 2,3,4 2009-2-5
d_one d_time_one d_two d_timetwo
1 2009-2-1 0
2 2009-2-2 1
3 2009-2-2 1
4 2009-2-5 1
5 null 2,3,4想要更新字段d_time_two,数据从d_time_one中更新来,更新的规则是:d_one的编号在d_two中,对应当d_time_one的最大值
其中d_two表示当前记录的父类编号
---想要的结果
d_one d_time_one d_two d_timetwo
1 2009-2-1 0
2 2009-2-2 1 2009-2-1
3 2009-2-2 1 2009-2-1
4 2009-2-5 1 2009-2-1
5 null 2,3,4 2009-2-5
set a.d_timetwo=b.d_time_one
比如如上数据:
第5条记录里面的数据,对应2,3,4中最大的数,有可能是2、也有可能3所对应的d_time_one 3楼有没有什么建议?具体一点
select *
from ttq4 a inner join ttq4 b on
if(instr(b.d_two,',')>0,
GREATEST(SUBSTRING_INDEX(b.d_two,',',1),
SUBSTRING_INDEX(SUBSTRING_INDEX(b.d_two,',',2),',',-1),
SUBSTRING_INDEX(SUBSTRING_INDEX(b.d_two,',',3),',',-1)
),b.d_two)=a.d_oneorupdate ttq4 a inner join ttq4 b on
if(instr(b.d_two,',')>0,
GREATEST(SUBSTRING_INDEX(b.d_two,',',1),
SUBSTRING_INDEX(SUBSTRING_INDEX(b.d_two,',',2),',',-1),
SUBSTRING_INDEX(SUBSTRING_INDEX(b.d_two,',',3),',',-1)
),b.d_two)=a.d_one
set b.d_timetwo=a.d_time_one
+-------+------------+-------+-----------+
| d_one | d_time_one | d_two | d_timetwo |
+-------+------------+-------+-----------+
| 1 | 2009-02-01 | 0 | NULL |
| 2 | 2009-02-02 | 1 | NULL |
| 3 | 2009-02-02 | 1 | NULL |
| 4 | 2009-02-05 | 1 | NULL |
| 5 | NULL | 2,3,4 | NULL |
+-------+------------+-------+-----------+
5 rows in set (0.00 sec)mysql>
mysql> update t_xluzhong t ,(
-> select a.d_one,(select d_time_one from t_xluzhong where d_one =(select max(d_one) from t_xluzhong where FIND_IN_SET(d_one,a.d_two))) as k
-> from t_xluzhong a) f
-> set t.d_timetwo=f.k
-> where t.d_one=f.d_one;
Query OK, 4 rows affected (0.09 sec)
Rows matched: 5 Changed: 4 Warnings: 0mysql> select * from t_xluzhong;
+-------+------------+-------+------------+
| d_one | d_time_one | d_two | d_timetwo |
+-------+------------+-------+------------+
| 1 | 2009-02-01 | 0 | NULL |
| 2 | 2009-02-02 | 1 | 2009-02-01 |
| 3 | 2009-02-02 | 1 | 2009-02-01 |
| 4 | 2009-02-05 | 1 | 2009-02-01 |
| 5 | NULL | 2,3,4 | 2009-02-05 |
+-------+------------+-------+------------+
5 rows in set (0.00 sec)mysql>
update ttq4 a1 inner join
(select a.d_one,max(a.d_time_one) ma from ttq4 a left join ttq4 b
on
find_in_set(a.d_one,b.d_two) group by a.d_one) b1
on a1.d_one=b1.d_one
set a1.d_timetwo=b1.ma
update ttq4 a1 inner join (
select a.d_one,max(b.d_time_one) as ma from ttq4 a left join ttq4 b
on
find_in_set(b.d_one,a.d_two)
group by a.d_one) b1
on a1.d_one=b1.d_one
set a1.d_timetwo=b1.ma