我有这样的数据:
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
2条答案
按热度按时间iq0todco1#
你可以整天和用户变量打交道,也可以在mysql中模拟密级函数,比如:
将上述查询中的“表”替换为原始查询中的子查询:
to94eoyn2#
请尝试使用此查询:
我创建了一个表测试并插入了如下记录:
我开发了查询: