SQL成绩统计排序实例教程

test 表

id epid name suject score

1 1 张小三 技术 89

2 1 张小三 理论 70

3 1 张小三 效率 79

4 2 李小四 技术 89

5 2 李小四 效率 30

6 2 李小四 理论 50

---

关键字

desc 降序

asc 升序

sum() 统计函数

计算每个员工的总分并排名（要求显示字段：姓名、总成绩） 降序

select sum(score), epid,name from test group by epid, name order by sum(score) desc

select name as 姓名,sum(score) as 总成绩 from test group by epid order by sum(score) desc

select name,sum(score) from test group by name order by sum(score) desc

计算每个员工的总分并排名（要求显示字段：工号、姓名、总成绩） 降序

select epid as 工号,name as 姓名,sum(score) as 总成绩 from test group by epid order by sum(score) desc

计算每个员工的总分并排名（要求显示字段：工号、姓名、总成绩） 升序

---

这块有点疑问？asc 为升序，为什么在中文姓名排序时变成了降序

select epid as 工号,name as 姓名,sum(score) as 总成绩 from test group by epid order by sum(score) asc

计算每个员工的总分并排名（要求显示字段：工号、姓名、总成绩） 按中文姓名首字排序 降序

select epid as 工号,name as 姓名,sum(score) as 总成绩 from test group by epid order by name asc

计算每个员工的总分并排名（要求显示字段：工号、姓名、总成绩） 按中文姓名首字排序 升序

select epid as 工号,name as 姓名,sum(score) as 总成绩 from test group by epid order by name desc

---