MYSQL数据排名与指定数据排名获取

极目楚天 2021-05-04 18:21:53
Mysql 排名 数据 定数 指定


今天主要与大家分享的是MySQL数据排名与指定条件数据排名的获取,数据的排名在我们做用户的排序或者其它排序的时候是比较常见的,当然也可以通过其它处理方式来进行数据排名的计算。这里我们主要与大家分享的是MySQl中的SQL语句的排名处理

现有一个发帖统计表结构:

CREATE TABLE `answers`.`posting_static` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(100) NULL DEFAULT '', `post` int(11) NULL DEFAULT 0, `created_at` timestamp NULL, `updated_at` datetime NULL, PRIMARY KEY (`id`));

数据实示例:


MYSQL数据排名与指定数据排名获取

我们要取得全部用户的发帖量的排行,怎么进行捏?

首先我们需要对用户的发帖量进行排序,对排序后的结果进行编号(也可以先查询后排序再编号),这个编号就是我们需要的排名,那么这个编号怎么编呢?

在Mysql中我们可以借助 @rownum 来实现,

@rownum := @rownum + 1 中 := 是赋值的作用,意思是先执行@rownum + 1,然后把值赋给@rownum。

(SELECT @rownum := 0) r 这句话的意思是设置rownum字段的初始值为0,即编号从1开始。

所有数据实现SQL:

SELECT a.*, @rownum := @rownum + 1 AS rank FROM (SELECT @rownum := 0) r, `posting_static` AS a ORDER BY a.post DESC;

按条件获取获取单个用户的排名:

SELECT b.* SELECT FROM(SELECT a.*, @rownum := @rownum + 1 AS rank FROM (SELECT @rownum := 0) r, `posting_static` AS a ORDER BY a.post DESC) as b where b.`username` = '王五';
版权声明
本文为[极目楚天]所创,转载请带上原文链接,感谢
https://www.seoxiehui.cn/article-318432-1.html

  1. Prototype与JQuery对比
  2. Three ways of data transmission between threads in Java and source code display
  3. Jdon causes 99% of CPU and Tomcat dies -- banq replies
  4. docker 原理之 user namespace(下)
  5. Simulating AOP injection with domain events
  6. Spring 3.1 finally adds cache support
  7. Comparison between prototype and jquery
  8. User namespace of docker principle (2)
  9. The way to learn java IO stream and XML
  10. Why does a seemingly correct code cause the Dubbo thread pool to be full
  11. 0 基础 Java 自学之路(2021年最新版)
  12. 0 basic Java self study road (latest version in 2021)
  13. c#—基础拾遗(1) 面向对象
  14. C - basic information (1) object oriented
  15. 技术分享|SQL和 NoSQL数据库之间的差异:MySQL(VS)MongoDB
  16. Technology sharing differences between SQL and NoSQL databases: MySQL (VS) mongodb
  17. PHP教程/面向对象-3~构造函数和析构函数
  18. Spring Cloud的Feign客户端入门
  19. 优化Spring Boot应用的Docker打包速度
  20. PHP tutorial / object oriented - 3 ~ constructor and destructor
  21. Introduction to feign client of spring cloud
  22. Optimizing docker packaging speed of spring boot application
  23. 尚硅谷宋红康Java基础教程2019版
  24. 尚硅谷宋红康Java基础教程2019版
  25. Song Hongkang Java foundation course 2019
  26. Song Hongkang Java foundation course 2019
  27. Redis 6 的多线程
  28. Multithreading of redis 6
  29. SpringCloud-微服务架构编码构建
  30. SpringCloud-微服务架构编码构建
  31. Linux作业控制
  32. Coding construction of springcloud microservice architecture
  33. Java中几个常用并发队列比较 | Baeldung
  34. 为什么Java后端在创业企业中并不流行? -reddit
  35. Coding construction of springcloud microservice architecture
  36. Linux job control
  37. Comparison of several common concurrent queues in Java
  38. Why is java backend not popular in start-ups- reddit
  39. docker 资源限制之 cgroup
  40. 大数据环境: hadoop和jdk部署
  41. CGroup of docker resource limitation
  42. Big data environment: Hadoop and JDK deployment
  43. Spring与Hibernate与JPA的整合(详细配置和源码)
  44. Integration of spring, hibernate and JPA (detailed configuration and source code)
  45. 《精通JPA与Hibernate:Java对象持久化技术详解》的源代码下载
  46. 《精通JPA与Hibernate:Java对象持久化技术详解》的源代码下载
  47. "Proficient in JPA and Hibernate: Java Object Persistence technology" source code download
  48. "Proficient in JPA and Hibernate: Java Object Persistence technology" source code download
  49. Redis、Kafka或RabbitMQ:选择哪个作为微服务消息代理? - otonomo
  50. Java Stream和Collection比较:何时以及如何从Java API返回Stream而不是集合Collection? - TomaszKiełbowicz
  51. 如何在SpringBoot中使用Hibernate @NaturalId?
  52. RPC框架设计----NIO编程通道(Channel)
  53. The use of jquery
  54. Redis, Kafka or rabbitmq: which one to choose as the micro service message broker- otonomo
  55. Comparison of Java stream and collection: when and how to return stream instead of collection from Java API- TomaszKie ł bowicz
  56. How to use hibernate @ naturalid in springboot?
  57. RPC framework design -- NiO programming channel
  58. RPC框架设计----NIO编程Selector (选择器)
  59. RPC framework design -- NiO programming selector
  60. Linux centos重启命令