MySQL data ranking and specified data ranking acquisition

Extreme vision 2021-05-04 18:23:13
mysql data ranking specified data

What I want to share with you today is MySQL Data ranking and the acquisition of data ranking under specified conditions , Data ranking is quite common when we do user ranking or other ranking , Of course, we can also calculate the data ranking through other processing methods . Here we mainly share with you MySQl Medium SQL Sentence ranking processing

There is a structure of posting statistics table :

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`));

Data example :

MYSQL Data ranking and specified data ranking

We want to get the ranking of all users' posts , How to pinch ?

First of all, we need to sort the number of users' posts , Number the sorted results ( You can also query first, then sort and number ), This number is the ranking we need , So how to make up this number ?

stay Mysql We can use @rownum To achieve ,

@rownum := @rownum + 1 in := It's the function of assignment , It means to execute first @rownum + 1, Then assign the value to @rownum.

(SELECT @rownum := 0) r The meaning of this sentence is set rownum The initial value of the field is 0, I.e. number from 1 Start .

All data implementation SQL:

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

Get the ranking of a single user by condition :

SELECT b.* SELECT FROM(SELECT a.*, @rownum := @rownum + 1 AS rank FROM (SELECT @rownum := 0) r, `posting_static` AS a ORDER BY DESC) as b where b.`username` = ' Wang Wu ';
本文为[Extreme vision]所创,转载请带上原文链接,感谢

  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重启命令