比较有挑战性的SQL语句,看看哪位高手能用一个SQL语句或者更简单的方法来完成表a:
id zimu type zorder
1 a 1 0
2 c 1 0
3 d 1 0
4 a 2 0
5 b 2 0
6 u 3 0
7 z 3 0
8 j 4 0
9 k 4 0
10 m 4 0
11 t 5 0
12 u 5 0要求更新表a的zorder字段,使之变成如下的效果
id zimu type zorder
1 a 1 1
2 c 1 2
3 d 1 3
4 a 2 1
5 b 2 2
6 u 3 1
7 z 3 2
8 j 4 1
9 k 4 2
10 m 4 3
11 t 5 1
12 u 5 2MSSQL方法,现求MYSQL方法
update t
set
zorder=(select count(*) from 表a where type=t.type and id<=t.id)
from
表a t
id zimu type zorder
1 a 1 0
2 c 1 0
3 d 1 0
4 a 2 0
5 b 2 0
6 u 3 0
7 z 3 0
8 j 4 0
9 k 4 0
10 m 4 0
11 t 5 0
12 u 5 0要求更新表a的zorder字段,使之变成如下的效果
id zimu type zorder
1 a 1 1
2 c 1 2
3 d 1 3
4 a 2 1
5 b 2 2
6 u 3 1
7 z 3 2
8 j 4 1
9 k 4 2
10 m 4 3
11 t 5 1
12 u 5 2MSSQL方法,现求MYSQL方法
update t
set
zorder=(select count(*) from 表a where type=t.type and id<=t.id)
from
表a t
insert into a values(1,'a',1, 0),
(2,'c',1 ,0),
(3,'d',1, 0),
(4,'a' ,2, 0),
(5, 'b', 2, 0),
(6, 'u', 3, 0),
(7, 'z', 3, 0),
(8, 'j', 4, 0),
(9, 'k', 4, 0),
(10, 'm', 4, 0),
(11,'t' ,5, 0),
(12, 'u', 5, 0);
create table b(id int(2),zimu char(1),type int(1),zorder int(2));
insert into b values(1,'a',1, 0),
(2,'c',1 ,0),
(3,'d',1, 0),
(4,'a' ,2, 0),
(5, 'b', 2, 0),
(6, 'u', 3, 0),
(7, 'z', 3, 0),
(8, 'j', 4, 0),
(9, 'k', 4, 0),
(10, 'm', 4, 0),
(11,'t' ,5, 0),
(12, 'u', 5, 0);update a,b set a.zorder=(select count(*) from b where a.type=b.type and a.id>=b.id);
select * from a;================
query result(12 records)
id zimu type zorder
1 a 1 1
2 c 1 2
3 d 1 3
4 a 2 1
5 b 2 2
6 u 3 1
7 z 3 2
8 j 4 1
9 k 4 2
10 m 4 3
11 t 5 1
12 u 5 2