按id字段值的大小给number字段赋1、2、3、4、5、6、7......值。
update tab c inner join
(select t.name, count(*) as ma from tab t
inner join tab b
on t.name =b.name and t.id <=b.id group by t.name) d
on c.name =d.name
set c.number =d.ma update tab,(select a.name, count(*) as cnt
from tab a inner join tab b on a.name =b.name and a.id<=b.id
group by a.name ) c
set tab.number=c.cnt
where tab.name=c.name执行以上两个sql语句后, number值不是1、2、3这样顺序的,而是符合姓名相同的集合数。
update tab
set number=(select count(*) from tab t
where t.name=tab.name and t.id<=patient.id)
执行上面sql 报1093 you can't specify target table 'tab' for update in from clause
据说是mysql限制。 用的mysql 4.1请教正确sql写法。
想要的结果是这样:name id number
---- ---- ----------------------- ----
李四 37 1
李四 39 2
李四 45 3
李四 77 4
王五 23 1
王五 25 2
刘六 66 1
update tab c inner join
(select t.name, count(*) as ma from tab t
inner join tab b
on t.name =b.name and t.id <=b.id group by t.name) d
on c.name =d.name
set c.number =d.ma update tab,(select a.name, count(*) as cnt
from tab a inner join tab b on a.name =b.name and a.id<=b.id
group by a.name ) c
set tab.number=c.cnt
where tab.name=c.name执行以上两个sql语句后, number值不是1、2、3这样顺序的,而是符合姓名相同的集合数。
update tab
set number=(select count(*) from tab t
where t.name=tab.name and t.id<=patient.id)
执行上面sql 报1093 you can't specify target table 'tab' for update in from clause
据说是mysql限制。 用的mysql 4.1请教正确sql写法。
想要的结果是这样:name id number
---- ---- ----------------------- ----
李四 37 1
李四 39 2
李四 45 3
李四 77 4
王五 23 1
王五 25 2
刘六 66 1
+----+------+--------+
| id | name | number |
+----+------+--------+
| 23 | 王五 | NULL |
| 25 | 王五 | NULL |
| 39 | 李四 | NULL |
| 45 | 李四 | NULL |
| 66 | 刘六 | NULL |
| 77 | 李四 | NULL |
+----+------+--------+
6 rows in set (0.00 sec)mysql>
mysql> update tab , ( select *,
-> (select count(*) from tab where name=t.name and id<=t.id) as sno
-> from tab t) u
-> set tab.number = u.sno
-> where tab.name=u.name and tab.id=u.id;
Query OK, 6 rows affected (0.11 sec)
Rows matched: 6 Changed: 6 Warnings: 0mysql> select * from tab order by name;
+----+------+--------+
| id | name | number |
+----+------+--------+
| 39 | 李四 | 1 |
| 45 | 李四 | 2 |
| 77 | 李四 | 3 |
| 66 | 刘六 | 1 |
| 23 | 王五 | 1 |
| 25 | 王五 | 2 |
+----+------+--------+
6 rows in set (0.00 sec)mysql>
select
name,
id,
number=(select count(1)+1 from tab where name=t.name and id<t.id)
from
tab t
update
tab a,
(select name,id,number=(select count(1)+1 from tab where name=t.name and id<t.id) from tab t) b
set
a.number=b.number
where
a.name=b.name
and
a.id=b.id
select a.name,a.id,count(b.id) from ttg5 a left join ttg5 b
on a.name=b.name and a.id>=b.id group by a.name,a.id2
update ttg5 a1,
(select a.name,a.id,count(b.id) as ma from ttg5 a left join ttg5 b
on a.name=b.name and a.id>=b.id group by a.name,a.id) b1
set a1.number=b1.ma
where a1.name=b1.name and a1.id=b1.id;update ttg5 a1 inner join
(select a.name,a.id,count(b.id) as ma from ttg5 a left join ttg5 b
on a.name=b.name and a.id>=b.id group by a.name,a.id) b1
on a1.name=b1.name and a1.id=b1.id;
set a1.number=b1.ma