如何在mysql中进行序号排序

8dtrkrch  于 2021-06-19  发布在  Mysql
关注(0)|答案(2)|浏览(546)

我有这样的数据:

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
iq0todco

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
to94eoyn

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 |

相关问题