需要用row_number来给分组添加序号。
1、创建测试表,插入数据:
create table test(sid int,sname varchar(20),sclass varchar(20),score int) insert into test values (1,'张三','一年一班',100)insert into test values (2,'李四','一年一班',78)insert into test values (3,'王五','一年一班',67)insert into test values (4,'赵六','一年一班',87)insert into test values (5,'badkano','一年二班',98)insert into test values (6,'百度知道团长','一年二班',99)insert into test values (7,'du小小动','一年二班',99)insert into test values (8,'刘备','一年三班',56)insert into test values (9,'张飞','一年三班',67)insert into test values (10,'关羽','一年三班',76)
1select row_number() over (order by score desc) 排名,sclass 班级,score 总分 from (select sclass,SUM(score) score from test group by sclass) t3、查询结果:
alter table abc add num int unsigned auto_increment如果同时为主键的话,可以这样写
alter table abc add num int unsigned primary key auto_increment
__________________________________________
to:数据库强者 不ok
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)