时间戳

首页 > 解决方案 > 如何在mysql中进行序列号排名

mysql - 如何在mysql中进行序列号排名

问题描述

我有这样的数据：

id | md_name      | total_visit
===+==============+============
1  | Nunu Nugraha | 33
2  | Erwin        | 32
3  | Tri Sulistyo | 35
4  | Risdianto    | 24
5  | Erma         | 22
6  | Dwi Sabana   | 19
7  | Ernayanti    | 26
8  | Ali          | 10
9  | Partini      | 13

我用这样的连接代码得到了上述结果：

SELECT datamd.id as id,
    datamd.nama_md as md_name,
    COUNT(R.id) as total_visit
    FROM datamd
    LEFT JOIN
    (
        SELECT id, idmd
        FROM rincian_kunjungan WHERE status='1' AND MONTH(tanggal_kunjungan)='$bulan' AND YEAR(tanggal_kunjungan)='$tahun'
    ) AS R
        ON datamd.id = R.idmd WHERE status=1 AND level=8 GROUP BY datamd.id ORDER BY datamd.id

我想变成这样：

rank | id | md_name      | total_visit
=====+====+==============+============
  1  | 3  | Tri Sulistyo | 35
  2  | 1  | Nunu Nurgaha | 33
  3  | 2  | Erwin        | 32
  4  | 7  | Ernayanti    | 26
  5  | 4  | Risdianto    | 24
  6  | 5  | Erma         | 22
  7  | 6  | Dwi Sabana   | 19
  8  | 9  | Partini      | 13
  9  | 8  | Ali          | 10

这里的朋友可以帮帮我吗，我已经尝试使用下面的代码但是在排名栏中它不合适

SET @number = 0;
SELECT @number:=@number+1 as rank, datamd.id as id,
    datamd.nama_md as md_name,
    COUNT(R.id) as total_visit
    FROM datamd
    LEFT JOIN
    (
        SELECT id, idmd
        FROM rincian_kunjungan WHERE status='1' AND MONTH(tanggal_kunjungan)='$bulan' AND YEAR(tanggal_kunjungan)='$tahun'
    ) AS R
        ON datamd.id = R.idmd WHERE status=1 AND level=8 GROUP BY datamd.id ORDER BY rank ASC

标签： mysqlsqlauto-incrementrow-numberdense-rank

解决方案

请尝试使用此查询：

我创建了一个表测试并插入了这样的记录：

select  * from test ;
+----+--------------+-------------+
| id | md_name      | total_visit |
+----+--------------+-------------+
|  1 | Nunu Nugraha |          33 |
|  2 | Erwin        |          32 |
|  3 | Tri Sulistyo |          35 |
|  4 | Risdianto    |          24 |
|  5 | Erma         |          22 |
|  6 | Dwi Sabana   |          19 |
|  7 | Ernayanti    |          26 |
|  8 | Ali          |          10 |
|  9 | Partini      |          13 |
+----+--------------+-------------+

我开发了查询：

SELECT @rownum := @rownum +1 rank, id, md_name, total_visit
FROM `test` , (SELECT @rownum :=0)r
ORDER BY total_visit DESC
LIMIT 0 , 30


got this result :

+------+----+--------------+-------------+
| rank | id | md_name      | total_visit |
+------+----+--------------+-------------+
|    1 |  3 | Tri Sulistyo |          35 |
|    2 |  1 | Nunu Nugraha |          33 |
|    3 |  2 | Erwin        |          32 |
|    4 |  7 | Ernayanti    |          26 |
|    5 |  4 | Risdianto    |          24 |
|    6 |  5 | Erma         |          22 |
|    7 |  6 | Dwi Sabana   |          19 |
|    8 |  9 | Partini      |          13 |
|    9 |  8 | Ali          |          10 |

推荐阅读