一个学生表中有一个字段是school
这个字段肯定是有重复的值的,比如一个学校有200多个学生在表上现在想给这个学生表增加一个字段,就是school_id
也就是从上到下逐步增加的id,当然所有重复的学校名自然也都是一样的id了比如学生 school school_id
小刚 不及格小学 1
艺谋 不及格小学 1
凯歌 不及格小学 1
小泽 很凉快小学 2
仓井 很凉快小学 2
郭刚 被封杀小学 3请各位指点
谢谢了
这个字段肯定是有重复的值的,比如一个学校有200多个学生在表上现在想给这个学生表增加一个字段,就是school_id
也就是从上到下逐步增加的id,当然所有重复的学校名自然也都是一样的id了比如学生 school school_id
小刚 不及格小学 1
艺谋 不及格小学 1
凯歌 不及格小学 1
小泽 很凉快小学 2
仓井 很凉快小学 2
郭刚 被封杀小学 3请各位指点
谢谢了
select s.学生,s.school,b.id
from school s join (select distinct school,@n:=@n+1 as school_id from school) b
on s.school=b.school ;
set @n=0;
select s.学生,s.school,b.id
from school s join (select distinct school,@n:=@n+1 as school_id from school) b
on s.school=b.school
order by b.id;
set @n=0;
select s.学生,s.school,b.school_id
from school s join (select distinct school,@n:=@n+1 as school_id from school) b
on s.school=b.school
order by b.school_id ;
请问如何set这个school_id呢?
谢谢
update s
set s.school_id=b.school_id
from school s join (select distinct school,@n:=@n+1 as school_id from school) b
on s.school=b.school
order by b.school_id ;试试
发现报错在
from school s join (select distinct school,@n:=@n+1 as school_id from school) b
on s.school=b.school
之间的地方
可我也看不出有什么错误唯一一点不解的是,
set s.county_id=b.county_id
是不是多对一啊,因为之前有distinct?
下面是我测试 写对的
DROP TABLE IF EXISTS test ;
CREATE TABLE test
(
name VARCHAR(10),
school VARCHAR(100),
school_id INT
);
INSERT test(name,school) SELECT
'小刚', '不及格小学' UNION ALL SELECT
'艺谋', '不及格小学' UNION ALL SELECT
'凯歌', '不及格小学' UNION ALL SELECT
'小泽', '很凉快小学' UNION ALL SELECT
'仓井', '很凉快小学' UNION ALL SELECT
'郭刚', '被封杀小学' ;
set @n=0;
update test s ,(select school,@n:=@n+1 as school_id from (SELECT DISTINCT school FROM test ) k) b
set s.school_id=b.school_id
WHERE s.school=b.school;mysql> select * from test ;
+------+------------+-----------+
| name | school | school_id |
+------+------------+-----------+
| 小刚 | 不及格小学 | 1 |
| 艺谋 | 不及格小学 | 1 |
| 凯歌 | 不及格小学 | 1 |
| 小泽 | 很凉快小学 | 2 |
| 仓井 | 很凉快小学 | 2 |
| 郭刚 | 被封杀小学 | 3 |
+------+------------+-----------+
6 rows in set (0.00 sec)
update all_school s, (select distinct school_county,@n:=@n+1 as county_id from (SELECT DISTINCT school_county FROM all_school ) k) b
set s.county_id=b.county_id
WHERE s.school_county=b.school_county