如何在mysql中进行序号排序

8dtrkrch 于 2021-06-19 发布在 Mysql

关注(0)|答案(2)|浏览(545)

我有这样的数据：

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

sql mysql auto-increment dense-rank row-number

来源：https://stackoverflow.com/questions/53368432/how-to-make-a-sequence-number-ranking-in-mysql

2条答案

按热度按时间

iq0todco1#

你可以整天和用户变量打交道，也可以在mysql中模拟密级函数，比如：

SELECT main.id, main.md_name, main.total_visit, COUNT(DISTINCT prev.total_visit) + 1 AS rank
FROM datamd AS main
LEFT JOIN datamd AS prev ON prev.total_visit > main.total_visit
GROUP BY main.id, main.md_name, main.total_visit
ORDER BY rank

将上述查询中的“表”替换为原始查询中的子查询：

SELECT datamd.id, datamd.nama_md, main.total_visit, COUNT(DISTINCT prev.total_visit) + 1 AS rank
FROM datamd
LEFT JOIN (
    SELECT idmd, COUNT(*) AS total_visit
    FROM rincian_kunjungan
    WHERE status = '1' AND MONTH(tanggal_kunjungan) = $bulan AND YEAR(tanggal_kunjungan) = $tahun
    GROUP BY idmd
) AS main ON datamd.id = main.idmd
LEFT JOIN (
    SELECT COUNT(*) AS total_visit
    FROM rincian_kunjungan
    WHERE status = '1' AND MONTH(tanggal_kunjungan) = $bulan AND YEAR(tanggal_kunjungan) = $tahun
    GROUP BY idmd
) AS prev ON prev.total_visit > main.total_visit
GROUP BY datamd.id, datamd.nama_md, main.total_visit
ORDER BY rank

赞(0）回复(0）举报 2021-06-19

to94eoyn2#

请尝试使用此查询：
我创建了一个表测试并插入了如下记录：

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 |

赞(0）回复(0）举报 2021-06-19

我来回答

如何在mysql中进行序号排序

2条答案

相关问题

热门标签

最新问答