关于MySQL排序SQL漏洞必看，很多小白/不熟悉不懂的踩坑无数次了！

参数排序方法，只能作用于临时表，不能作用于join的结果。不然报错！！！   

正确用法如下：

   set @p = null; set @r = 0;

select record_id,doctor_name,doctor_guid, rank,create_date
from
(
SELECT
s.*,
if(@p=s.record_id,@r:=@r+1,@r:=1) as rank,
@p:=s.record_id
from
            (
                   select a.record_id,a.doctor_name,a.doctor_guid,create_date,a.id
                   from hmcdss2.mt_patient_record_doctor a
                   join rp_update_gc_patient_info b on a.record_id=b.record_id
                   where doctor_type = 4 and source_type=1
            )s
order by s.record_id,s.id desc
) t
where rank = 1

错误用法：

set @p = null; set @r = 0;

select
record_id, doctor_name, doctor_guid
from

(

    select

a.record_id,a.doctor_name,a.doctor_guid,
if(@p=a.record_id,@r:=@r+1,@r:=1) as rank,
@p:=a.record_id
     from hmcdss2.mt_patient_record_doctor a
     join rp_update_gc_patient_info b on a.record_id=b.record_id
    where doctor_type = 2
    order by a.record_id,create_date desc
) t
where rank = 1