SQL 成绩排名

用户分数排名与可视化

最新推荐文章于 2023-07-09 17:23:05 发布

原创最新推荐文章于 2023-07-09 17:23:05 发布 · 179 阅读

0 ·

CC 4.0 BY-SA版权

mysql 同时被 2 个专栏收录

5 篇文章

订阅专栏

oracle

2 篇文章

订阅专栏

本文展示了如何使用SQL语句对用户分数进行排序，并通过图表进行可视化呈现。


CREATE TABLE `user_score` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `score` int(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

INSERT INTO `user_score` VALUES ('1', 'zhangshuling', '100');
INSERT INTO `user_score` VALUES ('2', 'ganhefang', '71');
INSERT INTO `user_score` VALUES ('3', 'zhangchunzhi', '60');
INSERT INTO `user_score` VALUES ('4', 'huangwei', '59');
INSERT INTO `user_score` VALUES ('5', 'gandan', '71');
INSERT INTO `user_score` VALUES ('6', 'ganyongqiang', '86');

排序语句


SELECT t1.*,t2.taxis FROM 
(SELECT us.id,COUNT(_us.id)+1 taxis FROM user_score us LEFT JOIN user_score _us ON us.score<_us.score GROUP BY us.id) t2 INNER JOIN user_score t1 ON t1.id = t2.id
ORDER BY t2.taxis ASC

效果如下:
[img]http://dl2.iteye.com/upload/attachment/0085/2983/db048c9a-3291-333f-812b-659d1c04d0e3.jpg[/img]